USE [Genesis]
GO

/****** Object:  StoredProcedure [dbo].[AuthorizeSession]    Script Date: 07/21/2009 21:55:44 ******/
CREATE PROCEDURE [dbo].[AuthorizeSession]
	@SessionGuid [uniqueidentifier]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[AuthorizeSession]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'AuthorizeSession'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\AuthorizeSession.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'AuthorizeSession'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'AuthorizeSession'
GO

/****** Object:  StoredProcedure [dbo].[AuthorizeUser]    Script Date: 07/21/2009 21:55:44 ******/
CREATE PROCEDURE [dbo].[AuthorizeUser]
	@Username [nvarchar](4000),
	@Password [nvarchar](4000),
	@ApplicationGuid [uniqueidentifier]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[AuthorizeUser]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'AuthorizeUser'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\AuthorizeUser.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'AuthorizeUser'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'AuthorizeUser'
GO

/****** Object:  StoredProcedure [dbo].[CompleteTransaction]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CompleteTransaction]
(
	@TransactionGuid UNIQUEIDENTIFIER
) AS

UPDATE Security_Transaction SET Completed = 1 , CompletedDateTime = GETDATE() WHERE TransactionGuid = @TransactionGuid

GO

/****** Object:  StoredProcedure [dbo].[CreateTransaction]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CreateTransaction]
(
	@TransactionTypeCode NVARCHAR(128),
	@SessionGuid UNIQUEIDENTIFIER
)
AS

SET @TransactionTypeCode = 'System.Query'
SET @SessionGuid = (SELECT TOP 1 SessionGuid FROM Security_Session ORDER BY ProcessDateTime DESC)

DECLARE @TransactionTypeGuid UNIQUEIDENTIFIER
DECLARE @ProcessDateTime DATETIME

SET @TransactionTypeGuid = (SELECT TOP 1 TransactionTypeGuid FROM Transaction_TransactionType WHERE LOWER(TransactionTypeCode) = @TransactionTypeCode)
SET @ProcessDateTime = GETDATE()

INSERT INTO Security_Transaction (SessionGuid, TransactionTypeGuid, ProcessDateTime) VALUES (@SessionGuid, @TransactionTypeGuid, @ProcessDateTime)

SELECT TOP 1 TransactionGuid FROM Security_Transaction WHERE SessionGuid = @SessionGuid AND TransactionTypeGuid = @TransactionTypeGuid AND ProcessDateTime = @ProcessDateTime AND Completed = 0 AND CompletedDateTime IS NULL ORDER BY ProcessDateTime DESC

GO

/****** Object:  StoredProcedure [dbo].[Delete_ModuleApplication]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_ModuleApplication]

				@ApplicationGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Module_Application]
      WHERE ApplicationGuid = @ApplicationGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_ModuleBarItem]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_ModuleBarItem]

				@BarItemGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Module_BarItem]
      WHERE BarItemGuid = @BarItemGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_ModuleCommand]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_ModuleCommand]

				@CommandGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Module_Command]
      WHERE CommandGuid = @CommandGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_ModuleFile]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_ModuleFile]

				@FileGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Module_File]
      WHERE FileGuid = @FileGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_ModuleRibbon]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_ModuleRibbon]

				@RibbonGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Module_Ribbon]
      WHERE RibbonGuid = @RibbonGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_ModuleRibbonBar]    Script Date: 07/21/2009 21:55:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_ModuleRibbonBar]

				@RibbonBarGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Module_RibbonBar]
      WHERE RibbonBarGuid = @RibbonBarGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_SecurityApplicationRole]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_SecurityApplicationRole]

				@ApplicationGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Security_ApplicationRole]
      WHERE ApplicationGuid = @ApplicationGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_SecurityCommandRole]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_SecurityCommandRole]

				@CommandGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Security_CommandRole]
      WHERE CommandGuid = @CommandGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_SecurityRibbonBarRole]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_SecurityRibbonBarRole]

				@RibbonBarGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Security_RibbonBarRole]
      WHERE RibbonBarGuid = @RibbonBarGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_SecurityRibbonRole]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_SecurityRibbonRole]

				@RibbonGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Security_RibbonRole]
      WHERE RibbonGuid = @RibbonGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_SecurityRole]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_SecurityRole]

				@RoleGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Security_Role]
      WHERE RoleGuid = @RoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_SecurityUser]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_SecurityUser]

				@UserGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Security_User]
      WHERE UserGuid = @UserGuid

GO

/****** Object:  StoredProcedure [dbo].[Delete_SecurityUserRole]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Delete_SecurityUserRole]

				@UserGuid uniqueidentifier

as

DELETE FROM [Genesis].[dbo].[Security_UserRole]
      WHERE UserGuid = @UserGuid

GO

/****** Object:  StoredProcedure [dbo].[Exception_LogEntry_Delete]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Exception_LogEntry table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Exception_LogEntry_Delete]
(

	@LogEntryGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Exception_LogEntry] WITH (ROWLOCK) 
				WHERE
					[LogEntryGuid] = @LogEntryGuid

GO

/****** Object:  StoredProcedure [dbo].[Exception_LogEntry_Find]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Exception_LogEntry table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Exception_LogEntry_Find]
(

	@SearchUsingOR bit   = null ,

	@LogEntryGuid uniqueidentifier   = null ,

	@LogEntryTypeId int   = null ,

	@LogEntryXml xml   = null ,

	@ProcessDateTime datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [LogEntryGuid]
	, [LogEntryTypeId]
	, [LogEntryXml]
	, [ProcessDateTime]
    FROM
	dbo.[Exception_LogEntry]
    WHERE 
	 ([LogEntryGuid] = @LogEntryGuid OR @LogEntryGuid is null)
	AND ([LogEntryTypeId] = @LogEntryTypeId OR @LogEntryTypeId is null)
	AND ([LogEntryXml] = @LogEntryXml OR @LogEntryXml is null)
	AND ([ProcessDateTime] = @ProcessDateTime OR @ProcessDateTime is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [LogEntryGuid]
	, [LogEntryTypeId]
	, [LogEntryXml]
	, [ProcessDateTime]
    FROM
	dbo.[Exception_LogEntry]
    WHERE 
	 ([LogEntryGuid] = @LogEntryGuid AND @LogEntryGuid is not null)
	OR ([LogEntryTypeId] = @LogEntryTypeId AND @LogEntryTypeId is not null)
	OR ([LogEntryXml] = @LogEntryXml AND @LogEntryXml is not null)
	OR ([ProcessDateTime] = @ProcessDateTime AND @ProcessDateTime is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Exception_LogEntry_Get_List]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Exception_LogEntry table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Exception_LogEntry_Get_List]

AS


				
				SELECT
					[LogEntryGuid],
					[LogEntryTypeId],
					[LogEntryXml],
					[ProcessDateTime]
				FROM
					dbo.[Exception_LogEntry]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Exception_LogEntry_GetByLogEntryGuid]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Exception_LogEntry table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Exception_LogEntry_GetByLogEntryGuid]
(

	@LogEntryGuid uniqueidentifier   
)
AS


				SELECT
					[LogEntryGuid],
					[LogEntryTypeId],
					[LogEntryXml],
					[ProcessDateTime]
				FROM
					dbo.[Exception_LogEntry]
				WHERE
					[LogEntryGuid] = @LogEntryGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Exception_LogEntry_GetPaged]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Exception_LogEntry table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Exception_LogEntry_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[LogEntryGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [LogEntryGuid]'
				SET @SQL = @SQL + ', [LogEntryTypeId]'
				SET @SQL = @SQL + ', [LogEntryXml]'
				SET @SQL = @SQL + ', [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM dbo.[Exception_LogEntry]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [LogEntryGuid],'
				SET @SQL = @SQL + ' [LogEntryTypeId],'
				SET @SQL = @SQL + ' [LogEntryXml],'
				SET @SQL = @SQL + ' [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Exception_LogEntry]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Exception_LogEntry_Insert]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Exception_LogEntry table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Exception_LogEntry_Insert]
(

	@LogEntryGuid uniqueidentifier   ,

	@LogEntryTypeId int   ,

	@LogEntryXml xml   ,

	@ProcessDateTime datetime   
)
AS


					
				INSERT INTO dbo.[Exception_LogEntry]
					(
					[LogEntryGuid]
					,[LogEntryTypeId]
					,[LogEntryXml]
					,[ProcessDateTime]
					)
				VALUES
					(
					@LogEntryGuid
					,@LogEntryTypeId
					,@LogEntryXml
					,@ProcessDateTime
					)

GO

/****** Object:  StoredProcedure [dbo].[Exception_LogEntry_Update]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Exception_LogEntry table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Exception_LogEntry_Update]
(

	@LogEntryGuid uniqueidentifier   ,

	@OriginalLogEntryGuid uniqueidentifier   ,

	@LogEntryTypeId int   ,

	@LogEntryXml xml   ,

	@ProcessDateTime datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Exception_LogEntry]
				SET
					[LogEntryGuid] = @LogEntryGuid
					,[LogEntryTypeId] = @LogEntryTypeId
					,[LogEntryXml] = @LogEntryXml
					,[ProcessDateTime] = @ProcessDateTime
				WHERE
[LogEntryGuid] = @OriginalLogEntryGuid

GO

/****** Object:  StoredProcedure [dbo].[Insert_Module_Application]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[Insert_Module_Application]

(			@ApplicationName nvarchar(50)
           ,@ApplicationDescription ntext
           ,@ApplicationOrder int
           ,@ApplicationVisible bit
)

as
INSERT INTO [Genesis].[dbo].[Module_Application]
           ([ApplicationName]
           ,[ApplicationDescription]
           ,[ApplicationOrder]
           ,[ApplicationVisible])
     VALUES
           (@ApplicationName
           ,@ApplicationDescription
           ,@ApplicationOrder
           ,@ApplicationVisible)
         

SELECT [Applicationguid],[applicationid] FROM [Genesis].[dbo].[Module_Application] WHERE [Applicationid] = @@IDENTITY

GO

/****** Object:  StoredProcedure [dbo].[Insert_Module_BarItem]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[Insert_Module_BarItem]
			@ParentBarItemGuid uniqueidentifier
           ,@RibbonBarGuid uniqueidentifier
           ,@CommandGuid uniqueidentifier
           ,@BarItemName nvarchar(128)
           ,@BarItemImage nvarchar(1024)
           ,@BarItemImageSize int
           ,@BarItemTypeId int
           ,@BarItemOrder int
as
INSERT INTO [Genesis].[dbo].[Module_BarItem]
--           [BarItemGuid]
           ([ParentBarItemGuid]
           ,[RibbonBarGuid]
           ,[CommandGuid]
           ,[BarItemName]
           ,[BarItemImage]
           ,[BarItemImageSize]
           ,[BarItemTypeId]
           ,[BarItemOrder])
     VALUES
--           (@BarItemGuid
           (@ParentBarItemGuid
           ,@RibbonBarGuid
           ,@CommandGuid
           ,@BarItemName
           ,@BarItemImage
           ,@BarItemImageSize
           ,@BarItemTypeId
           ,@BarItemOrder)

SELECT [BarItemGuid],[BarItemID] FROM [Genesis].[dbo].[Module_BarItem] WHERE [BarItemID] = @@IDENTITY

GO

/****** Object:  StoredProcedure [dbo].[insert_Module_Command]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[insert_Module_Command]
		
            @FileGuid uniqueidentifier
            ,@CommandName nvarchar(50)
            ,@CommandCode nvarchar(50)
            ,@CommandDescription ntext
            ,@CommandType nvarchar(512)

as
INSERT INTO [Genesis].[dbo].[Module_Command]
           ([FileGuid]
           ,[CommandName]
           ,[CommandCode]
           ,[CommandDescription]
           ,[CommandType])

     VALUES
           (@FileGuid 
           ,@CommandName 
           ,@CommandCode 
           ,@CommandDescription 
           ,@CommandType )

select [CommandGuid],[CommandID] FROM [Genesis].[dbo].[Module_Command] WHERE [CommandID] = @@IDENTITY

GO

/****** Object:  StoredProcedure [dbo].[Insert_Module_File]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Insert_Module_File]
			
            @ApplicationGuid uniqueidentifier
           ,@FileName nvarchar(256)
           ,@FileDescription ntext
           ,@FileUrl nvarchar(1024)
           ,@FilePath nvarchar(256)
           ,@FileVersionMajor int
           ,@FileVersionMinor int
           ,@FileVersionBuild int
           ,@FileVersionRevision int
           ,@FileTypeId int
           ,@FileOrder int
as

INSERT INTO [Genesis].[dbo].[Module_File]
           ([ApplicationGuid]
           ,[FileName]
           ,[FileDescription]
           ,[FileUrl]
           ,[FilePath]
           ,[FileVersionMajor]
           ,[FileVersionMinor]
           ,[FileVersionBuild]
           ,[FileVersionRevision]
           ,[FileTypeId]
           ,[FileOrder])
     VALUES
           (@ApplicationGuid 
           ,@FileName
           ,@FileDescription 
           ,@FileUrl 
           ,@FilePath
           ,@FileVersionMajor 
           ,@FileVersionMinor 
           ,@FileVersionBuild 
           ,@FileVersionRevision 
           ,@FileTypeId 
           ,@FileOrder )

SELECT [FileGuid],[FileId] FROM [Genesis].[dbo].[Module_File] WHERE [FileId] = @@IDENTITY

GO

/****** Object:  StoredProcedure [dbo].[Insert_Module_Ribbon]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[Insert_Module_Ribbon]
(		   
			@RibbonName nvarchar(50)
           ,@ApplicationGuid uniqueidentifier
           ,@RibbonOrder int
)

as

INSERT INTO [Genesis].[dbo].[Module_Ribbon]
           ([RibbonName]
           ,[ApplicationGuid]
           ,[RibbonOrder])
     VALUES
           (@RibbonName
           ,@ApplicationGuid
           ,@RibbonOrder)

SELECT [RibbonGuid],[Ribbonid] FROM [Genesis].[dbo].[Module_Ribbon] WHERE [Ribbonid] = @@IDENTITY

GO

/****** Object:  StoredProcedure [dbo].[Insert_Module_RibbonBar]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[Insert_Module_RibbonBar]
(		   
			@RibbonBarName nvarchar(50)
           ,@RibbonGuid uniqueidentifier
           ,@RibbonBarOrder int
)

as

INSERT INTO [Genesis].[dbo].[Module_RibbonBar]
           ([RibbonBarName]
           ,[RibbonGuid]
           ,[RibbonBarOrder])
     VALUES
           (@RibbonBarName
           ,@RibbonGuid
           ,@RibbonBarOrder)

SELECT [RibbonBarGuid],[RibbonBarid] FROM [Genesis].[dbo].[Module_RibbonBar] WHERE [RibbonBarid] = @@IDENTITY

GO

/****** Object:  StoredProcedure [dbo].[Insert_SecurityUser]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[Insert_SecurityUser]

(			@Username nvarchar(50)
           ,@Password nvarchar(255)
           ,@email nvarchar(128)
           ,@Active bit
           ,@StartupScript nvarchar(255)
)

as
INSERT INTO [Genesis].[dbo].[Security_User]
           ([Username]
           ,[Password]
           ,[email]
           ,[Active]
           ,[StartupScript])
     VALUES
           (@Username
           ,@Password
           ,@email
           ,1
           ,'http://genesis.eliyahu.co.za/genesis.filehost/eliyahulite/scripts/logon.xml')

SELECT [userguid],[userid] FROM [Genesis].[dbo].[Security_User] WHERE [userid] = @@IDENTITY

GO

/****** Object:  StoredProcedure [dbo].[Lookup_BarItemType_Delete]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Lookup_BarItemType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_BarItemType_Delete]
(

	@BarItemTypeId int   
)
AS


				DELETE FROM dbo.[Lookup_BarItemType] WITH (ROWLOCK) 
				WHERE
					[BarItemTypeId] = @BarItemTypeId

GO

/****** Object:  StoredProcedure [dbo].[Lookup_BarItemType_Find]    Script Date: 07/21/2009 21:55:45 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Lookup_BarItemType table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_BarItemType_Find]
(

	@SearchUsingOR bit   = null ,

	@BarItemTypeId int   = null ,

	@BarItemTypeName nvarchar (30)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [BarItemTypeId]
	, [BarItemTypeName]
    FROM
	dbo.[Lookup_BarItemType]
    WHERE 
	 ([BarItemTypeId] = @BarItemTypeId OR @BarItemTypeId is null)
	AND ([BarItemTypeName] = @BarItemTypeName OR @BarItemTypeName is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [BarItemTypeId]
	, [BarItemTypeName]
    FROM
	dbo.[Lookup_BarItemType]
    WHERE 
	 ([BarItemTypeId] = @BarItemTypeId AND @BarItemTypeId is not null)
	OR ([BarItemTypeName] = @BarItemTypeName AND @BarItemTypeName is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Lookup_BarItemType_Get_List]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Lookup_BarItemType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_BarItemType_Get_List]

AS


				
				SELECT
					[BarItemTypeId],
					[BarItemTypeName]
				FROM
					dbo.[Lookup_BarItemType]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Lookup_BarItemType_GetByBarItemTypeId]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Lookup_BarItemType table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_BarItemType_GetByBarItemTypeId]
(

	@BarItemTypeId int   
)
AS


				SELECT
					[BarItemTypeId],
					[BarItemTypeName]
				FROM
					dbo.[Lookup_BarItemType]
				WHERE
					[BarItemTypeId] = @BarItemTypeId
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Lookup_BarItemType_GetPaged]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Lookup_BarItemType table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_BarItemType_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[BarItemTypeId]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [BarItemTypeId]'
				SET @SQL = @SQL + ', [BarItemTypeName]'
				SET @SQL = @SQL + ' FROM dbo.[Lookup_BarItemType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [BarItemTypeId],'
				SET @SQL = @SQL + ' [BarItemTypeName]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Lookup_BarItemType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Lookup_BarItemType_Insert]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Lookup_BarItemType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_BarItemType_Insert]
(

	@BarItemTypeId int    OUTPUT,

	@BarItemTypeName nvarchar (30)  
)
AS


					
				INSERT INTO dbo.[Lookup_BarItemType]
					(
					[BarItemTypeName]
					)
				VALUES
					(
					@BarItemTypeName
					)
				
				-- Get the identity value
				SET @BarItemTypeId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Lookup_BarItemType_Update]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Lookup_BarItemType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_BarItemType_Update]
(

	@BarItemTypeId int   ,

	@BarItemTypeName nvarchar (30)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Lookup_BarItemType]
				SET
					[BarItemTypeName] = @BarItemTypeName
				WHERE
[BarItemTypeId] = @BarItemTypeId

GO

/****** Object:  StoredProcedure [dbo].[Lookup_FileType_Delete]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Lookup_FileType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_FileType_Delete]
(

	@FileTypeId int   
)
AS


				DELETE FROM dbo.[Lookup_FileType] WITH (ROWLOCK) 
				WHERE
					[FileTypeId] = @FileTypeId

GO

/****** Object:  StoredProcedure [dbo].[Lookup_FileType_Find]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Lookup_FileType table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_FileType_Find]
(

	@SearchUsingOR bit   = null ,

	@FileTypeId int   = null ,

	@FileTypeName nvarchar (30)  = null ,

	@FileTypeDescription ntext   = null ,

	@FileTypeExtension nvarchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [FileTypeId]
	, [FileTypeName]
	, [FileTypeDescription]
	, [FileTypeExtension]
    FROM
	dbo.[Lookup_FileType]
    WHERE 
	 ([FileTypeId] = @FileTypeId OR @FileTypeId is null)
	AND ([FileTypeName] = @FileTypeName OR @FileTypeName is null)
	AND ([FileTypeExtension] = @FileTypeExtension OR @FileTypeExtension is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [FileTypeId]
	, [FileTypeName]
	, [FileTypeDescription]
	, [FileTypeExtension]
    FROM
	dbo.[Lookup_FileType]
    WHERE 
	 ([FileTypeId] = @FileTypeId AND @FileTypeId is not null)
	OR ([FileTypeName] = @FileTypeName AND @FileTypeName is not null)
	OR ([FileTypeExtension] = @FileTypeExtension AND @FileTypeExtension is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Lookup_FileType_Get_List]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Lookup_FileType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_FileType_Get_List]

AS


				
				SELECT
					[FileTypeId],
					[FileTypeName],
					[FileTypeDescription],
					[FileTypeExtension]
				FROM
					dbo.[Lookup_FileType]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Lookup_FileType_GetByFileTypeId]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Lookup_FileType table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_FileType_GetByFileTypeId]
(

	@FileTypeId int   
)
AS


				SELECT
					[FileTypeId],
					[FileTypeName],
					[FileTypeDescription],
					[FileTypeExtension]
				FROM
					dbo.[Lookup_FileType]
				WHERE
					[FileTypeId] = @FileTypeId
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Lookup_FileType_GetPaged]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Lookup_FileType table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_FileType_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[FileTypeId]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [FileTypeId]'
				SET @SQL = @SQL + ', [FileTypeName]'
				SET @SQL = @SQL + ', [FileTypeDescription]'
				SET @SQL = @SQL + ', [FileTypeExtension]'
				SET @SQL = @SQL + ' FROM dbo.[Lookup_FileType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [FileTypeId],'
				SET @SQL = @SQL + ' [FileTypeName],'
				SET @SQL = @SQL + ' [FileTypeDescription],'
				SET @SQL = @SQL + ' [FileTypeExtension]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Lookup_FileType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Lookup_FileType_Insert]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Lookup_FileType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_FileType_Insert]
(

	@FileTypeId int    OUTPUT,

	@FileTypeName nvarchar (30)  ,

	@FileTypeDescription ntext   ,

	@FileTypeExtension nvarchar (50)  
)
AS


					
				INSERT INTO dbo.[Lookup_FileType]
					(
					[FileTypeName]
					,[FileTypeDescription]
					,[FileTypeExtension]
					)
				VALUES
					(
					@FileTypeName
					,@FileTypeDescription
					,@FileTypeExtension
					)
				
				-- Get the identity value
				SET @FileTypeId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Lookup_FileType_Update]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Lookup_FileType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Lookup_FileType_Update]
(

	@FileTypeId int   ,

	@FileTypeName nvarchar (30)  ,

	@FileTypeDescription ntext   ,

	@FileTypeExtension nvarchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Lookup_FileType]
				SET
					[FileTypeName] = @FileTypeName
					,[FileTypeDescription] = @FileTypeDescription
					,[FileTypeExtension] = @FileTypeExtension
				WHERE
[FileTypeId] = @FileTypeId

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_Delete]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Module_Application table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_Delete]
(

	@ApplicationGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Module_Application] WITH (ROWLOCK) 
				WHERE
					[ApplicationGuid] = @ApplicationGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_Find]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Module_Application table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_Find]
(

	@SearchUsingOR bit   = null ,

	@ApplicationGuid uniqueidentifier   = null ,

	@ApplicationId int   = null ,

	@ApplicationName nvarchar (50)  = null ,

	@ApplicationDescription ntext   = null ,

	@ApplicationOrder int   = null ,

	@ApplicationVisible bit   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ApplicationGuid]
	, [ApplicationId]
	, [ApplicationName]
	, [ApplicationDescription]
	, [ApplicationOrder]
	, [ApplicationVisible]
    FROM
	dbo.[Module_Application]
    WHERE 
	 ([ApplicationGuid] = @ApplicationGuid OR @ApplicationGuid is null)
	AND ([ApplicationId] = @ApplicationId OR @ApplicationId is null)
	AND ([ApplicationName] = @ApplicationName OR @ApplicationName is null)
	AND ([ApplicationOrder] = @ApplicationOrder OR @ApplicationOrder is null)
	AND ([ApplicationVisible] = @ApplicationVisible OR @ApplicationVisible is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ApplicationGuid]
	, [ApplicationId]
	, [ApplicationName]
	, [ApplicationDescription]
	, [ApplicationOrder]
	, [ApplicationVisible]
    FROM
	dbo.[Module_Application]
    WHERE 
	 ([ApplicationGuid] = @ApplicationGuid AND @ApplicationGuid is not null)
	OR ([ApplicationId] = @ApplicationId AND @ApplicationId is not null)
	OR ([ApplicationName] = @ApplicationName AND @ApplicationName is not null)
	OR ([ApplicationOrder] = @ApplicationOrder AND @ApplicationOrder is not null)
	OR ([ApplicationVisible] = @ApplicationVisible AND @ApplicationVisible is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_Get_List]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Module_Application table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_Get_List]

AS


				
				SELECT
					[ApplicationGuid],
					[ApplicationId],
					[ApplicationName],
					[ApplicationDescription],
					[ApplicationOrder],
					[ApplicationVisible]
				FROM
					dbo.[Module_Application]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_GetByApplicationGuid]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_Application table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_GetByApplicationGuid]
(

	@ApplicationGuid uniqueidentifier   
)
AS


				SELECT
					[ApplicationGuid],
					[ApplicationId],
					[ApplicationName],
					[ApplicationDescription],
					[ApplicationOrder],
					[ApplicationVisible]
				FROM
					dbo.[Module_Application]
				WHERE
					[ApplicationGuid] = @ApplicationGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_GetByRoleGuidFromSecurity_ApplicationRole]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_GetByRoleGuidFromSecurity_ApplicationRole]
(

	@RoleGuid uniqueidentifier   
)
AS


SELECT dbo.[Module_Application].[ApplicationGuid]
       ,dbo.[Module_Application].[ApplicationId]
       ,dbo.[Module_Application].[ApplicationName]
       ,dbo.[Module_Application].[ApplicationDescription]
       ,dbo.[Module_Application].[ApplicationOrder]
       ,dbo.[Module_Application].[ApplicationVisible]
  FROM dbo.[Module_Application]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_ApplicationRole] 
                WHERE dbo.[Security_ApplicationRole].[RoleGuid] = @RoleGuid
                  AND dbo.[Security_ApplicationRole].[ApplicationGuid] = dbo.[Module_Application].[ApplicationGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_GetPaged]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Module_Application table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[ApplicationGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [ApplicationGuid]'
				SET @SQL = @SQL + ', [ApplicationId]'
				SET @SQL = @SQL + ', [ApplicationName]'
				SET @SQL = @SQL + ', [ApplicationDescription]'
				SET @SQL = @SQL + ', [ApplicationOrder]'
				SET @SQL = @SQL + ', [ApplicationVisible]'
				SET @SQL = @SQL + ' FROM dbo.[Module_Application]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [ApplicationGuid],'
				SET @SQL = @SQL + ' [ApplicationId],'
				SET @SQL = @SQL + ' [ApplicationName],'
				SET @SQL = @SQL + ' [ApplicationDescription],'
				SET @SQL = @SQL + ' [ApplicationOrder],'
				SET @SQL = @SQL + ' [ApplicationVisible]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Module_Application]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_Insert]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Module_Application table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_Insert]
(

	@ApplicationGuid uniqueidentifier   ,

	@ApplicationId int    OUTPUT,

	@ApplicationName nvarchar (50)  ,

	@ApplicationDescription ntext   ,

	@ApplicationOrder int   ,

	@ApplicationVisible bit   
)
AS


					
				INSERT INTO dbo.[Module_Application]
					(
					[ApplicationGuid]
					,[ApplicationName]
					,[ApplicationDescription]
					,[ApplicationOrder]
					,[ApplicationVisible]
					)
				VALUES
					(
					@ApplicationGuid
					,@ApplicationName
					,@ApplicationDescription
					,@ApplicationOrder
					,@ApplicationVisible
					)
				
				-- Get the identity value
				SET @ApplicationId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Module_Application_Update]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Module_Application table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Application_Update]
(

	@ApplicationGuid uniqueidentifier   ,

	@OriginalApplicationGuid uniqueidentifier   ,

	@ApplicationId int   ,

	@ApplicationName nvarchar (50)  ,

	@ApplicationDescription ntext   ,

	@ApplicationOrder int   ,

	@ApplicationVisible bit   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Module_Application]
				SET
					[ApplicationGuid] = @ApplicationGuid
					,[ApplicationName] = @ApplicationName
					,[ApplicationDescription] = @ApplicationDescription
					,[ApplicationOrder] = @ApplicationOrder
					,[ApplicationVisible] = @ApplicationVisible
				WHERE
[ApplicationGuid] = @OriginalApplicationGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_Delete]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Module_BarItem table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_Delete]
(

	@BarItemGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Module_BarItem] WITH (ROWLOCK) 
				WHERE
					[BarItemGuid] = @BarItemGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_Find]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Module_BarItem table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_Find]
(

	@SearchUsingOR bit   = null ,

	@BarItemGuid uniqueidentifier   = null ,

	@BarItemId int   = null ,

	@ParentBarItemGuid uniqueidentifier   = null ,

	@RibbonBarGuid uniqueidentifier   = null ,

	@CommandGuid uniqueidentifier   = null ,

	@BarItemName nvarchar (128)  = null ,

	@BarItemImage nvarchar (1024)  = null ,

	@BarItemImageSize int   = null ,

	@BarItemTypeId int   = null ,

	@BarItemOrder int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [BarItemGuid]
	, [BarItemId]
	, [ParentBarItemGuid]
	, [RibbonBarGuid]
	, [CommandGuid]
	, [BarItemName]
	, [BarItemImage]
	, [BarItemImageSize]
	, [BarItemTypeId]
	, [BarItemOrder]
    FROM
	dbo.[Module_BarItem]
    WHERE 
	 ([BarItemGuid] = @BarItemGuid OR @BarItemGuid is null)
	AND ([BarItemId] = @BarItemId OR @BarItemId is null)
	AND ([ParentBarItemGuid] = @ParentBarItemGuid OR @ParentBarItemGuid is null)
	AND ([RibbonBarGuid] = @RibbonBarGuid OR @RibbonBarGuid is null)
	AND ([CommandGuid] = @CommandGuid OR @CommandGuid is null)
	AND ([BarItemName] = @BarItemName OR @BarItemName is null)
	AND ([BarItemImage] = @BarItemImage OR @BarItemImage is null)
	AND ([BarItemImageSize] = @BarItemImageSize OR @BarItemImageSize is null)
	AND ([BarItemTypeId] = @BarItemTypeId OR @BarItemTypeId is null)
	AND ([BarItemOrder] = @BarItemOrder OR @BarItemOrder is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [BarItemGuid]
	, [BarItemId]
	, [ParentBarItemGuid]
	, [RibbonBarGuid]
	, [CommandGuid]
	, [BarItemName]
	, [BarItemImage]
	, [BarItemImageSize]
	, [BarItemTypeId]
	, [BarItemOrder]
    FROM
	dbo.[Module_BarItem]
    WHERE 
	 ([BarItemGuid] = @BarItemGuid AND @BarItemGuid is not null)
	OR ([BarItemId] = @BarItemId AND @BarItemId is not null)
	OR ([ParentBarItemGuid] = @ParentBarItemGuid AND @ParentBarItemGuid is not null)
	OR ([RibbonBarGuid] = @RibbonBarGuid AND @RibbonBarGuid is not null)
	OR ([CommandGuid] = @CommandGuid AND @CommandGuid is not null)
	OR ([BarItemName] = @BarItemName AND @BarItemName is not null)
	OR ([BarItemImage] = @BarItemImage AND @BarItemImage is not null)
	OR ([BarItemImageSize] = @BarItemImageSize AND @BarItemImageSize is not null)
	OR ([BarItemTypeId] = @BarItemTypeId AND @BarItemTypeId is not null)
	OR ([BarItemOrder] = @BarItemOrder AND @BarItemOrder is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_Get_List]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Module_BarItem table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_Get_List]

AS


				
				SELECT
					[BarItemGuid],
					[BarItemId],
					[ParentBarItemGuid],
					[RibbonBarGuid],
					[CommandGuid],
					[BarItemName],
					[BarItemImage],
					[BarItemImageSize],
					[BarItemTypeId],
					[BarItemOrder]
				FROM
					dbo.[Module_BarItem]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_Get_WithNullRecord]    Script Date: 07/21/2009 21:55:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[Module_BarItem_Get_WithNullRecord]
AS

	SELECT 
		'00000000-0000-0000-0000-000000000000' AS BarItemGuid, 
		'<Root Item>' AS BarItemName 
		
	UNION 
	
	SELECT 
		MBI.BarItemGuid, 
		MR.RibbonName + ' - ' + MRB.RibbonBarName + ' - ' + MBI.BarItemName AS BarItemName 
		
	FROM Module_BarItem MBI 
		INNER JOIN Module_RibbonBar MRB ON MRB.RibbonBarGuid = MBI.RibbonBarGuid 
		INNER JOIN Module_Ribbon MR ON MR.RibbonGuid = MRB.RibbonGuid 
		

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_GetByBarItemGuid]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_BarItem table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_GetByBarItemGuid]
(

	@BarItemGuid uniqueidentifier   
)
AS


				SELECT
					[BarItemGuid],
					[BarItemId],
					[ParentBarItemGuid],
					[RibbonBarGuid],
					[CommandGuid],
					[BarItemName],
					[BarItemImage],
					[BarItemImageSize],
					[BarItemTypeId],
					[BarItemOrder]
				FROM
					dbo.[Module_BarItem]
				WHERE
					[BarItemGuid] = @BarItemGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_GetByBarItemTypeId]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_BarItem table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_GetByBarItemTypeId]
(

	@BarItemTypeId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[BarItemGuid],
					[BarItemId],
					[ParentBarItemGuid],
					[RibbonBarGuid],
					[CommandGuid],
					[BarItemName],
					[BarItemImage],
					[BarItemImageSize],
					[BarItemTypeId],
					[BarItemOrder]
				FROM
					dbo.[Module_BarItem]
				WHERE
					[BarItemTypeId] = @BarItemTypeId
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_GetByCommandGuid]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_BarItem table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_GetByCommandGuid]
(

	@CommandGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[BarItemGuid],
					[BarItemId],
					[ParentBarItemGuid],
					[RibbonBarGuid],
					[CommandGuid],
					[BarItemName],
					[BarItemImage],
					[BarItemImageSize],
					[BarItemTypeId],
					[BarItemOrder]
				FROM
					dbo.[Module_BarItem]
				WHERE
					[CommandGuid] = @CommandGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_GetByParentBarItemGuid]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_BarItem table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_GetByParentBarItemGuid]
(

	@ParentBarItemGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[BarItemGuid],
					[BarItemId],
					[ParentBarItemGuid],
					[RibbonBarGuid],
					[CommandGuid],
					[BarItemName],
					[BarItemImage],
					[BarItemImageSize],
					[BarItemTypeId],
					[BarItemOrder]
				FROM
					dbo.[Module_BarItem]
				WHERE
					[ParentBarItemGuid] = @ParentBarItemGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_GetByRibbonBarGuid]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_BarItem table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_GetByRibbonBarGuid]
(

	@RibbonBarGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[BarItemGuid],
					[BarItemId],
					[ParentBarItemGuid],
					[RibbonBarGuid],
					[CommandGuid],
					[BarItemName],
					[BarItemImage],
					[BarItemImageSize],
					[BarItemTypeId],
					[BarItemOrder]
				FROM
					dbo.[Module_BarItem]
				WHERE
					[RibbonBarGuid] = @RibbonBarGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_GetPaged]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Module_BarItem table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[BarItemGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [BarItemGuid]'
				SET @SQL = @SQL + ', [BarItemId]'
				SET @SQL = @SQL + ', [ParentBarItemGuid]'
				SET @SQL = @SQL + ', [RibbonBarGuid]'
				SET @SQL = @SQL + ', [CommandGuid]'
				SET @SQL = @SQL + ', [BarItemName]'
				SET @SQL = @SQL + ', [BarItemImage]'
				SET @SQL = @SQL + ', [BarItemImageSize]'
				SET @SQL = @SQL + ', [BarItemTypeId]'
				SET @SQL = @SQL + ', [BarItemOrder]'
				SET @SQL = @SQL + ' FROM dbo.[Module_BarItem]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [BarItemGuid],'
				SET @SQL = @SQL + ' [BarItemId],'
				SET @SQL = @SQL + ' [ParentBarItemGuid],'
				SET @SQL = @SQL + ' [RibbonBarGuid],'
				SET @SQL = @SQL + ' [CommandGuid],'
				SET @SQL = @SQL + ' [BarItemName],'
				SET @SQL = @SQL + ' [BarItemImage],'
				SET @SQL = @SQL + ' [BarItemImageSize],'
				SET @SQL = @SQL + ' [BarItemTypeId],'
				SET @SQL = @SQL + ' [BarItemOrder]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Module_BarItem]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_Insert]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Module_BarItem table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_Insert]
(

	@BarItemGuid uniqueidentifier   ,

	@BarItemId int    OUTPUT,

	@ParentBarItemGuid uniqueidentifier   ,

	@RibbonBarGuid uniqueidentifier   ,

	@CommandGuid uniqueidentifier   ,

	@BarItemName nvarchar (128)  ,

	@BarItemImage nvarchar (1024)  ,

	@BarItemImageSize int   ,

	@BarItemTypeId int   ,

	@BarItemOrder int   
)
AS


					
				INSERT INTO dbo.[Module_BarItem]
					(
					[BarItemGuid]
					,[ParentBarItemGuid]
					,[RibbonBarGuid]
					,[CommandGuid]
					,[BarItemName]
					,[BarItemImage]
					,[BarItemImageSize]
					,[BarItemTypeId]
					,[BarItemOrder]
					)
				VALUES
					(
					@BarItemGuid
					,@ParentBarItemGuid
					,@RibbonBarGuid
					,@CommandGuid
					,@BarItemName
					,@BarItemImage
					,@BarItemImageSize
					,@BarItemTypeId
					,@BarItemOrder
					)
				
				-- Get the identity value
				SET @BarItemId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Module_BarItem_Update]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Module_BarItem table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_BarItem_Update]
(

	@BarItemGuid uniqueidentifier   ,

	@OriginalBarItemGuid uniqueidentifier   ,

	@BarItemId int   ,

	@ParentBarItemGuid uniqueidentifier   ,

	@RibbonBarGuid uniqueidentifier   ,

	@CommandGuid uniqueidentifier   ,

	@BarItemName nvarchar (128)  ,

	@BarItemImage nvarchar (1024)  ,

	@BarItemImageSize int   ,

	@BarItemTypeId int   ,

	@BarItemOrder int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Module_BarItem]
				SET
					[BarItemGuid] = @BarItemGuid
					,[ParentBarItemGuid] = @ParentBarItemGuid
					,[RibbonBarGuid] = @RibbonBarGuid
					,[CommandGuid] = @CommandGuid
					,[BarItemName] = @BarItemName
					,[BarItemImage] = @BarItemImage
					,[BarItemImageSize] = @BarItemImageSize
					,[BarItemTypeId] = @BarItemTypeId
					,[BarItemOrder] = @BarItemOrder
				WHERE
[BarItemGuid] = @OriginalBarItemGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_Delete]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Module_Command table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_Delete]
(

	@CommandGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Module_Command] WITH (ROWLOCK) 
				WHERE
					[CommandGuid] = @CommandGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_Find]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Module_Command table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_Find]
(

	@SearchUsingOR bit   = null ,

	@CommandGuid uniqueidentifier   = null ,

	@CommandId int   = null ,

	@FileGuid uniqueidentifier   = null ,

	@CommandName nvarchar (1024)  = null ,

	@CommandCode nvarchar (1024)  = null ,

	@CommandDescription ntext   = null ,

	@CommandType nvarchar (2048)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [CommandGuid]
	, [CommandId]
	, [FileGuid]
	, [CommandName]
	, [CommandCode]
	, [CommandDescription]
	, [CommandType]
    FROM
	dbo.[Module_Command]
    WHERE 
	 ([CommandGuid] = @CommandGuid OR @CommandGuid is null)
	AND ([CommandId] = @CommandId OR @CommandId is null)
	AND ([FileGuid] = @FileGuid OR @FileGuid is null)
	AND ([CommandName] = @CommandName OR @CommandName is null)
	AND ([CommandCode] = @CommandCode OR @CommandCode is null)
	AND ([CommandType] = @CommandType OR @CommandType is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [CommandGuid]
	, [CommandId]
	, [FileGuid]
	, [CommandName]
	, [CommandCode]
	, [CommandDescription]
	, [CommandType]
    FROM
	dbo.[Module_Command]
    WHERE 
	 ([CommandGuid] = @CommandGuid AND @CommandGuid is not null)
	OR ([CommandId] = @CommandId AND @CommandId is not null)
	OR ([FileGuid] = @FileGuid AND @FileGuid is not null)
	OR ([CommandName] = @CommandName AND @CommandName is not null)
	OR ([CommandCode] = @CommandCode AND @CommandCode is not null)
	OR ([CommandType] = @CommandType AND @CommandType is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_Get_List]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Module_Command table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_Get_List]

AS


				
				SELECT
					[CommandGuid],
					[CommandId],
					[FileGuid],
					[CommandName],
					[CommandCode],
					[CommandDescription],
					[CommandType]
				FROM
					dbo.[Module_Command]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_Get_WithNullRecord]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[Module_Command_Get_WithNullRecord]
AS

	SELECT 
		'00000000-0000-0000-0000-000000000000' AS CommandGuid, 
		'<Select Item>' AS CommandName 
		
	FROM 
		Module_Command 
	
	UNION 
	
	SELECT 
		CommandGuid, 
		CommandName 
		
	FROM 
		Module_Command 
		
	ORDER BY 
		CommandName


GO

/****** Object:  StoredProcedure [dbo].[Module_Command_GetByCommandGuid]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_Command table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_GetByCommandGuid]
(

	@CommandGuid uniqueidentifier   
)
AS


				SELECT
					[CommandGuid],
					[CommandId],
					[FileGuid],
					[CommandName],
					[CommandCode],
					[CommandDescription],
					[CommandType]
				FROM
					dbo.[Module_Command]
				WHERE
					[CommandGuid] = @CommandGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_GetByRoleGuidFromSecurity_CommandRole]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_GetByRoleGuidFromSecurity_CommandRole]
(

	@RoleGuid uniqueidentifier   
)
AS


SELECT dbo.[Module_Command].[CommandGuid]
       ,dbo.[Module_Command].[CommandId]
       ,dbo.[Module_Command].[FileGuid]
       ,dbo.[Module_Command].[CommandName]
       ,dbo.[Module_Command].[CommandCode]
       ,dbo.[Module_Command].[CommandDescription]
       ,dbo.[Module_Command].[CommandType]
  FROM dbo.[Module_Command]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_CommandRole] 
                WHERE dbo.[Security_CommandRole].[RoleGuid] = @RoleGuid
                  AND dbo.[Security_CommandRole].[CommandGuid] = dbo.[Module_Command].[CommandGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_GetPaged]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Module_Command table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[CommandGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [CommandGuid]'
				SET @SQL = @SQL + ', [CommandId]'
				SET @SQL = @SQL + ', [FileGuid]'
				SET @SQL = @SQL + ', [CommandName]'
				SET @SQL = @SQL + ', [CommandCode]'
				SET @SQL = @SQL + ', [CommandDescription]'
				SET @SQL = @SQL + ', [CommandType]'
				SET @SQL = @SQL + ' FROM dbo.[Module_Command]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [CommandGuid],'
				SET @SQL = @SQL + ' [CommandId],'
				SET @SQL = @SQL + ' [FileGuid],'
				SET @SQL = @SQL + ' [CommandName],'
				SET @SQL = @SQL + ' [CommandCode],'
				SET @SQL = @SQL + ' [CommandDescription],'
				SET @SQL = @SQL + ' [CommandType]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Module_Command]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_Insert]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Module_Command table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_Insert]
(

	@CommandGuid uniqueidentifier   ,

	@CommandId int    OUTPUT,

	@FileGuid uniqueidentifier   ,

	@CommandName nvarchar (1024)  ,

	@CommandCode nvarchar (1024)  ,

	@CommandDescription ntext   ,

	@CommandType nvarchar (2048)  
)
AS


					
				INSERT INTO dbo.[Module_Command]
					(
					[CommandGuid]
					,[FileGuid]
					,[CommandName]
					,[CommandCode]
					,[CommandDescription]
					,[CommandType]
					)
				VALUES
					(
					@CommandGuid
					,@FileGuid
					,@CommandName
					,@CommandCode
					,@CommandDescription
					,@CommandType
					)
				
				-- Get the identity value
				SET @CommandId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Module_Command_Update]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Module_Command table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Command_Update]
(

	@CommandGuid uniqueidentifier   ,

	@OriginalCommandGuid uniqueidentifier   ,

	@CommandId int   ,

	@FileGuid uniqueidentifier   ,

	@CommandName nvarchar (1024)  ,

	@CommandCode nvarchar (1024)  ,

	@CommandDescription ntext   ,

	@CommandType nvarchar (2048)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Module_Command]
				SET
					[CommandGuid] = @CommandGuid
					,[FileGuid] = @FileGuid
					,[CommandName] = @CommandName
					,[CommandCode] = @CommandCode
					,[CommandDescription] = @CommandDescription
					,[CommandType] = @CommandType
				WHERE
[CommandGuid] = @OriginalCommandGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_File_Delete]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Module_File table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_Delete]
(

	@FileGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Module_File] WITH (ROWLOCK) 
				WHERE
					[FileGuid] = @FileGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_File_Find]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Module_File table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_Find]
(

	@SearchUsingOR bit   = null ,

	@FileGuid uniqueidentifier   = null ,

	@FileId int   = null ,

	@ApplicationGuid uniqueidentifier   = null ,

	@FileName nvarchar (1024)  = null ,

	@FileDescription ntext   = null ,

	@FileUrl nvarchar (2048)  = null ,

	@FilePath nvarchar (1024)  = null ,

	@FileVersionMajor int   = null ,

	@FileVersionMinor int   = null ,

	@FileVersionBuild int   = null ,

	@FileVersionRevision int   = null ,

	@FileTypeId int   = null ,

	@FileOrder int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [FileGuid]
	, [FileId]
	, [ApplicationGuid]
	, [FileName]
	, [FileDescription]
	, [FileUrl]
	, [FilePath]
	, [FileVersionMajor]
	, [FileVersionMinor]
	, [FileVersionBuild]
	, [FileVersionRevision]
	, [FileTypeId]
	, [FileOrder]
    FROM
	dbo.[Module_File]
    WHERE 
	 ([FileGuid] = @FileGuid OR @FileGuid is null)
	AND ([FileId] = @FileId OR @FileId is null)
	AND ([ApplicationGuid] = @ApplicationGuid OR @ApplicationGuid is null)
	AND ([FileName] = @FileName OR @FileName is null)
	AND ([FileUrl] = @FileUrl OR @FileUrl is null)
	AND ([FilePath] = @FilePath OR @FilePath is null)
	AND ([FileVersionMajor] = @FileVersionMajor OR @FileVersionMajor is null)
	AND ([FileVersionMinor] = @FileVersionMinor OR @FileVersionMinor is null)
	AND ([FileVersionBuild] = @FileVersionBuild OR @FileVersionBuild is null)
	AND ([FileVersionRevision] = @FileVersionRevision OR @FileVersionRevision is null)
	AND ([FileTypeId] = @FileTypeId OR @FileTypeId is null)
	AND ([FileOrder] = @FileOrder OR @FileOrder is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [FileGuid]
	, [FileId]
	, [ApplicationGuid]
	, [FileName]
	, [FileDescription]
	, [FileUrl]
	, [FilePath]
	, [FileVersionMajor]
	, [FileVersionMinor]
	, [FileVersionBuild]
	, [FileVersionRevision]
	, [FileTypeId]
	, [FileOrder]
    FROM
	dbo.[Module_File]
    WHERE 
	 ([FileGuid] = @FileGuid AND @FileGuid is not null)
	OR ([FileId] = @FileId AND @FileId is not null)
	OR ([ApplicationGuid] = @ApplicationGuid AND @ApplicationGuid is not null)
	OR ([FileName] = @FileName AND @FileName is not null)
	OR ([FileUrl] = @FileUrl AND @FileUrl is not null)
	OR ([FilePath] = @FilePath AND @FilePath is not null)
	OR ([FileVersionMajor] = @FileVersionMajor AND @FileVersionMajor is not null)
	OR ([FileVersionMinor] = @FileVersionMinor AND @FileVersionMinor is not null)
	OR ([FileVersionBuild] = @FileVersionBuild AND @FileVersionBuild is not null)
	OR ([FileVersionRevision] = @FileVersionRevision AND @FileVersionRevision is not null)
	OR ([FileTypeId] = @FileTypeId AND @FileTypeId is not null)
	OR ([FileOrder] = @FileOrder AND @FileOrder is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Module_File_Get_List]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Module_File table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_Get_List]

AS


				
				SELECT
					[FileGuid],
					[FileId],
					[ApplicationGuid],
					[FileName],
					[FileDescription],
					[FileUrl],
					[FilePath],
					[FileVersionMajor],
					[FileVersionMinor],
					[FileVersionBuild],
					[FileVersionRevision],
					[FileTypeId],
					[FileOrder]
				FROM
					dbo.[Module_File]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_File_GetByApplicationGuid]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_File table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_GetByApplicationGuid]
(

	@ApplicationGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[FileGuid],
					[FileId],
					[ApplicationGuid],
					[FileName],
					[FileDescription],
					[FileUrl],
					[FilePath],
					[FileVersionMajor],
					[FileVersionMinor],
					[FileVersionBuild],
					[FileVersionRevision],
					[FileTypeId],
					[FileOrder]
				FROM
					dbo.[Module_File]
				WHERE
					[ApplicationGuid] = @ApplicationGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_File_GetByFileGuid]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_File table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_GetByFileGuid]
(

	@FileGuid uniqueidentifier   
)
AS


				SELECT
					[FileGuid],
					[FileId],
					[ApplicationGuid],
					[FileName],
					[FileDescription],
					[FileUrl],
					[FilePath],
					[FileVersionMajor],
					[FileVersionMinor],
					[FileVersionBuild],
					[FileVersionRevision],
					[FileTypeId],
					[FileOrder]
				FROM
					dbo.[Module_File]
				WHERE
					[FileGuid] = @FileGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_File_GetByFileTypeId]    Script Date: 07/21/2009 21:55:47 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_File table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_GetByFileTypeId]
(

	@FileTypeId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[FileGuid],
					[FileId],
					[ApplicationGuid],
					[FileName],
					[FileDescription],
					[FileUrl],
					[FilePath],
					[FileVersionMajor],
					[FileVersionMinor],
					[FileVersionBuild],
					[FileVersionRevision],
					[FileTypeId],
					[FileOrder]
				FROM
					dbo.[Module_File]
				WHERE
					[FileTypeId] = @FileTypeId
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_File_GetPaged]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Module_File table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[FileGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [FileGuid]'
				SET @SQL = @SQL + ', [FileId]'
				SET @SQL = @SQL + ', [ApplicationGuid]'
				SET @SQL = @SQL + ', [FileName]'
				SET @SQL = @SQL + ', [FileDescription]'
				SET @SQL = @SQL + ', [FileUrl]'
				SET @SQL = @SQL + ', [FilePath]'
				SET @SQL = @SQL + ', [FileVersionMajor]'
				SET @SQL = @SQL + ', [FileVersionMinor]'
				SET @SQL = @SQL + ', [FileVersionBuild]'
				SET @SQL = @SQL + ', [FileVersionRevision]'
				SET @SQL = @SQL + ', [FileTypeId]'
				SET @SQL = @SQL + ', [FileOrder]'
				SET @SQL = @SQL + ' FROM dbo.[Module_File]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [FileGuid],'
				SET @SQL = @SQL + ' [FileId],'
				SET @SQL = @SQL + ' [ApplicationGuid],'
				SET @SQL = @SQL + ' [FileName],'
				SET @SQL = @SQL + ' [FileDescription],'
				SET @SQL = @SQL + ' [FileUrl],'
				SET @SQL = @SQL + ' [FilePath],'
				SET @SQL = @SQL + ' [FileVersionMajor],'
				SET @SQL = @SQL + ' [FileVersionMinor],'
				SET @SQL = @SQL + ' [FileVersionBuild],'
				SET @SQL = @SQL + ' [FileVersionRevision],'
				SET @SQL = @SQL + ' [FileTypeId],'
				SET @SQL = @SQL + ' [FileOrder]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Module_File]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Module_File_Insert]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Module_File table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_Insert]
(

	@FileGuid uniqueidentifier   ,

	@FileId int    OUTPUT,

	@ApplicationGuid uniqueidentifier   ,

	@FileName nvarchar (1024)  ,

	@FileDescription ntext   ,

	@FileUrl nvarchar (2048)  ,

	@FilePath nvarchar (1024)  ,

	@FileVersionMajor int   ,

	@FileVersionMinor int   ,

	@FileVersionBuild int   ,

	@FileVersionRevision int   ,

	@FileTypeId int   ,

	@FileOrder int   
)
AS


					
				INSERT INTO dbo.[Module_File]
					(
					[FileGuid]
					,[ApplicationGuid]
					,[FileName]
					,[FileDescription]
					,[FileUrl]
					,[FilePath]
					,[FileVersionMajor]
					,[FileVersionMinor]
					,[FileVersionBuild]
					,[FileVersionRevision]
					,[FileTypeId]
					,[FileOrder]
					)
				VALUES
					(
					@FileGuid
					,@ApplicationGuid
					,@FileName
					,@FileDescription
					,@FileUrl
					,@FilePath
					,@FileVersionMajor
					,@FileVersionMinor
					,@FileVersionBuild
					,@FileVersionRevision
					,@FileTypeId
					,@FileOrder
					)
				
				-- Get the identity value
				SET @FileId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Module_File_Update]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Module_File table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_File_Update]
(

	@FileGuid uniqueidentifier   ,

	@OriginalFileGuid uniqueidentifier   ,

	@FileId int   ,

	@ApplicationGuid uniqueidentifier   ,

	@FileName nvarchar (1024)  ,

	@FileDescription ntext   ,

	@FileUrl nvarchar (2048)  ,

	@FilePath nvarchar (1024)  ,

	@FileVersionMajor int   ,

	@FileVersionMinor int   ,

	@FileVersionBuild int   ,

	@FileVersionRevision int   ,

	@FileTypeId int   ,

	@FileOrder int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Module_File]
				SET
					[FileGuid] = @FileGuid
					,[ApplicationGuid] = @ApplicationGuid
					,[FileName] = @FileName
					,[FileDescription] = @FileDescription
					,[FileUrl] = @FileUrl
					,[FilePath] = @FilePath
					,[FileVersionMajor] = @FileVersionMajor
					,[FileVersionMinor] = @FileVersionMinor
					,[FileVersionBuild] = @FileVersionBuild
					,[FileVersionRevision] = @FileVersionRevision
					,[FileTypeId] = @FileTypeId
					,[FileOrder] = @FileOrder
				WHERE
[FileGuid] = @OriginalFileGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_Delete]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Module_Ribbon table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_Delete]
(

	@RibbonGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Module_Ribbon] WITH (ROWLOCK) 
				WHERE
					[RibbonGuid] = @RibbonGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_Find]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Module_Ribbon table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_Find]
(

	@SearchUsingOR bit   = null ,

	@RibbonGuid uniqueidentifier   = null ,

	@RibbonId int   = null ,

	@ApplicationGuid uniqueidentifier   = null ,

	@RibbonName nvarchar (50)  = null ,

	@RibbonOrder int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [RibbonGuid]
	, [RibbonId]
	, [ApplicationGuid]
	, [RibbonName]
	, [RibbonOrder]
    FROM
	dbo.[Module_Ribbon]
    WHERE 
	 ([RibbonGuid] = @RibbonGuid OR @RibbonGuid is null)
	AND ([RibbonId] = @RibbonId OR @RibbonId is null)
	AND ([ApplicationGuid] = @ApplicationGuid OR @ApplicationGuid is null)
	AND ([RibbonName] = @RibbonName OR @RibbonName is null)
	AND ([RibbonOrder] = @RibbonOrder OR @RibbonOrder is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [RibbonGuid]
	, [RibbonId]
	, [ApplicationGuid]
	, [RibbonName]
	, [RibbonOrder]
    FROM
	dbo.[Module_Ribbon]
    WHERE 
	 ([RibbonGuid] = @RibbonGuid AND @RibbonGuid is not null)
	OR ([RibbonId] = @RibbonId AND @RibbonId is not null)
	OR ([ApplicationGuid] = @ApplicationGuid AND @ApplicationGuid is not null)
	OR ([RibbonName] = @RibbonName AND @RibbonName is not null)
	OR ([RibbonOrder] = @RibbonOrder AND @RibbonOrder is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_Get_List]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Module_Ribbon table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_Get_List]

AS


				
				SELECT
					[RibbonGuid],
					[RibbonId],
					[ApplicationGuid],
					[RibbonName],
					[RibbonOrder]
				FROM
					dbo.[Module_Ribbon]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_GetByApplicationGuid]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_Ribbon table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_GetByApplicationGuid]
(

	@ApplicationGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[RibbonGuid],
					[RibbonId],
					[ApplicationGuid],
					[RibbonName],
					[RibbonOrder]
				FROM
					dbo.[Module_Ribbon]
				WHERE
					[ApplicationGuid] = @ApplicationGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_GetByRibbonGuid]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_Ribbon table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_GetByRibbonGuid]
(

	@RibbonGuid uniqueidentifier   
)
AS


				SELECT
					[RibbonGuid],
					[RibbonId],
					[ApplicationGuid],
					[RibbonName],
					[RibbonOrder]
				FROM
					dbo.[Module_Ribbon]
				WHERE
					[RibbonGuid] = @RibbonGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_GetByRoleGuidFromSecurity_RibbonRole]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_GetByRoleGuidFromSecurity_RibbonRole]
(

	@RoleGuid uniqueidentifier   
)
AS


SELECT dbo.[Module_Ribbon].[RibbonGuid]
       ,dbo.[Module_Ribbon].[RibbonId]
       ,dbo.[Module_Ribbon].[ApplicationGuid]
       ,dbo.[Module_Ribbon].[RibbonName]
       ,dbo.[Module_Ribbon].[RibbonOrder]
  FROM dbo.[Module_Ribbon]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_RibbonRole] 
                WHERE dbo.[Security_RibbonRole].[RoleGuid] = @RoleGuid
                  AND dbo.[Security_RibbonRole].[RibbonGuid] = dbo.[Module_Ribbon].[RibbonGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_GetPaged]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Module_Ribbon table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[RibbonGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [RibbonGuid]'
				SET @SQL = @SQL + ', [RibbonId]'
				SET @SQL = @SQL + ', [ApplicationGuid]'
				SET @SQL = @SQL + ', [RibbonName]'
				SET @SQL = @SQL + ', [RibbonOrder]'
				SET @SQL = @SQL + ' FROM dbo.[Module_Ribbon]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [RibbonGuid],'
				SET @SQL = @SQL + ' [RibbonId],'
				SET @SQL = @SQL + ' [ApplicationGuid],'
				SET @SQL = @SQL + ' [RibbonName],'
				SET @SQL = @SQL + ' [RibbonOrder]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Module_Ribbon]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_Insert]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Module_Ribbon table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_Insert]
(

	@RibbonGuid uniqueidentifier   ,

	@RibbonId int    OUTPUT,

	@ApplicationGuid uniqueidentifier   ,

	@RibbonName nvarchar (50)  ,

	@RibbonOrder int   
)
AS


					
				INSERT INTO dbo.[Module_Ribbon]
					(
					[RibbonGuid]
					,[ApplicationGuid]
					,[RibbonName]
					,[RibbonOrder]
					)
				VALUES
					(
					@RibbonGuid
					,@ApplicationGuid
					,@RibbonName
					,@RibbonOrder
					)
				
				-- Get the identity value
				SET @RibbonId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Module_Ribbon_Update]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Module_Ribbon table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_Ribbon_Update]
(

	@RibbonGuid uniqueidentifier   ,

	@OriginalRibbonGuid uniqueidentifier   ,

	@RibbonId int   ,

	@ApplicationGuid uniqueidentifier   ,

	@RibbonName nvarchar (50)  ,

	@RibbonOrder int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Module_Ribbon]
				SET
					[RibbonGuid] = @RibbonGuid
					,[ApplicationGuid] = @ApplicationGuid
					,[RibbonName] = @RibbonName
					,[RibbonOrder] = @RibbonOrder
				WHERE
[RibbonGuid] = @OriginalRibbonGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_Delete]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Module_RibbonBar table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_Delete]
(

	@RibbonBarGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Module_RibbonBar] WITH (ROWLOCK) 
				WHERE
					[RibbonBarGuid] = @RibbonBarGuid

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_Find]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Module_RibbonBar table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_Find]
(

	@SearchUsingOR bit   = null ,

	@RibbonBarGuid uniqueidentifier   = null ,

	@RibbonBarId int   = null ,

	@RibbonGuid uniqueidentifier   = null ,

	@RibbonBarName nvarchar (50)  = null ,

	@RibbonBarOrder int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [RibbonBarGuid]
	, [RibbonBarId]
	, [RibbonGuid]
	, [RibbonBarName]
	, [RibbonBarOrder]
    FROM
	dbo.[Module_RibbonBar]
    WHERE 
	 ([RibbonBarGuid] = @RibbonBarGuid OR @RibbonBarGuid is null)
	AND ([RibbonBarId] = @RibbonBarId OR @RibbonBarId is null)
	AND ([RibbonGuid] = @RibbonGuid OR @RibbonGuid is null)
	AND ([RibbonBarName] = @RibbonBarName OR @RibbonBarName is null)
	AND ([RibbonBarOrder] = @RibbonBarOrder OR @RibbonBarOrder is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [RibbonBarGuid]
	, [RibbonBarId]
	, [RibbonGuid]
	, [RibbonBarName]
	, [RibbonBarOrder]
    FROM
	dbo.[Module_RibbonBar]
    WHERE 
	 ([RibbonBarGuid] = @RibbonBarGuid AND @RibbonBarGuid is not null)
	OR ([RibbonBarId] = @RibbonBarId AND @RibbonBarId is not null)
	OR ([RibbonGuid] = @RibbonGuid AND @RibbonGuid is not null)
	OR ([RibbonBarName] = @RibbonBarName AND @RibbonBarName is not null)
	OR ([RibbonBarOrder] = @RibbonBarOrder AND @RibbonBarOrder is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_Get_List]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Module_RibbonBar table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_Get_List]

AS


				
				SELECT
					[RibbonBarGuid],
					[RibbonBarId],
					[RibbonGuid],
					[RibbonBarName],
					[RibbonBarOrder]
				FROM
					dbo.[Module_RibbonBar]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_GetByRibbonBarGuid]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_RibbonBar table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_GetByRibbonBarGuid]
(

	@RibbonBarGuid uniqueidentifier   
)
AS


				SELECT
					[RibbonBarGuid],
					[RibbonBarId],
					[RibbonGuid],
					[RibbonBarName],
					[RibbonBarOrder]
				FROM
					dbo.[Module_RibbonBar]
				WHERE
					[RibbonBarGuid] = @RibbonBarGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_GetByRibbonGuid]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Module_RibbonBar table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_GetByRibbonGuid]
(

	@RibbonGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[RibbonBarGuid],
					[RibbonBarId],
					[RibbonGuid],
					[RibbonBarName],
					[RibbonBarOrder]
				FROM
					dbo.[Module_RibbonBar]
				WHERE
					[RibbonGuid] = @RibbonGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_GetByRoleGuidFromSecurity_RibbonBarRole]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_GetByRoleGuidFromSecurity_RibbonBarRole]
(

	@RoleGuid uniqueidentifier   
)
AS


SELECT dbo.[Module_RibbonBar].[RibbonBarGuid]
       ,dbo.[Module_RibbonBar].[RibbonBarId]
       ,dbo.[Module_RibbonBar].[RibbonGuid]
       ,dbo.[Module_RibbonBar].[RibbonBarName]
       ,dbo.[Module_RibbonBar].[RibbonBarOrder]
  FROM dbo.[Module_RibbonBar]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_RibbonBarRole] 
                WHERE dbo.[Security_RibbonBarRole].[RoleGuid] = @RoleGuid
                  AND dbo.[Security_RibbonBarRole].[RibbonBarGuid] = dbo.[Module_RibbonBar].[RibbonBarGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_GetPaged]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Module_RibbonBar table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[RibbonBarGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [RibbonBarGuid]'
				SET @SQL = @SQL + ', [RibbonBarId]'
				SET @SQL = @SQL + ', [RibbonGuid]'
				SET @SQL = @SQL + ', [RibbonBarName]'
				SET @SQL = @SQL + ', [RibbonBarOrder]'
				SET @SQL = @SQL + ' FROM dbo.[Module_RibbonBar]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [RibbonBarGuid],'
				SET @SQL = @SQL + ' [RibbonBarId],'
				SET @SQL = @SQL + ' [RibbonGuid],'
				SET @SQL = @SQL + ' [RibbonBarName],'
				SET @SQL = @SQL + ' [RibbonBarOrder]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Module_RibbonBar]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_Insert]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Module_RibbonBar table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_Insert]
(

	@RibbonBarGuid uniqueidentifier   ,

	@RibbonBarId int    OUTPUT,

	@RibbonGuid uniqueidentifier   ,

	@RibbonBarName nvarchar (50)  ,

	@RibbonBarOrder int   
)
AS


					
				INSERT INTO dbo.[Module_RibbonBar]
					(
					[RibbonBarGuid]
					,[RibbonGuid]
					,[RibbonBarName]
					,[RibbonBarOrder]
					)
				VALUES
					(
					@RibbonBarGuid
					,@RibbonGuid
					,@RibbonBarName
					,@RibbonBarOrder
					)
				
				-- Get the identity value
				SET @RibbonBarId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Module_RibbonBar_Update]    Script Date: 07/21/2009 21:55:48 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Module_RibbonBar table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Module_RibbonBar_Update]
(

	@RibbonBarGuid uniqueidentifier   ,

	@OriginalRibbonBarGuid uniqueidentifier   ,

	@RibbonBarId int   ,

	@RibbonGuid uniqueidentifier   ,

	@RibbonBarName nvarchar (50)  ,

	@RibbonBarOrder int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Module_RibbonBar]
				SET
					[RibbonBarGuid] = @RibbonBarGuid
					,[RibbonGuid] = @RibbonGuid
					,[RibbonBarName] = @RibbonBarName
					,[RibbonBarOrder] = @RibbonBarOrder
				WHERE
[RibbonBarGuid] = @OriginalRibbonBarGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_Delete]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_ApplicationRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_Delete]
(

	@ApplicationGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_ApplicationRole] WITH (ROWLOCK) 
				WHERE
					[ApplicationGuid] = @ApplicationGuid
					AND [RoleGuid] = @RoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_Find]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_ApplicationRole table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_Find]
(

	@SearchUsingOR bit   = null ,

	@ApplicationGuid uniqueidentifier   = null ,

	@RoleGuid uniqueidentifier   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [ApplicationGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_ApplicationRole]
    WHERE 
	 ([ApplicationGuid] = @ApplicationGuid OR @ApplicationGuid is null)
	AND ([RoleGuid] = @RoleGuid OR @RoleGuid is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [ApplicationGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_ApplicationRole]
    WHERE 
	 ([ApplicationGuid] = @ApplicationGuid AND @ApplicationGuid is not null)
	OR ([RoleGuid] = @RoleGuid AND @RoleGuid is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_Get_List]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_ApplicationRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_Get_List]

AS


				
				SELECT
					[ApplicationGuid],
					[RoleGuid]
				FROM
					dbo.[Security_ApplicationRole]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_GetByApplicationGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_ApplicationRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_GetByApplicationGuid]
(

	@ApplicationGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[ApplicationGuid],
					[RoleGuid]
				FROM
					dbo.[Security_ApplicationRole]
				WHERE
					[ApplicationGuid] = @ApplicationGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_GetByApplicationGuidRoleGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_ApplicationRole table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_GetByApplicationGuidRoleGuid]
(

	@ApplicationGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				SELECT
					[ApplicationGuid],
					[RoleGuid]
				FROM
					dbo.[Security_ApplicationRole]
				WHERE
					[ApplicationGuid] = @ApplicationGuid
					AND [RoleGuid] = @RoleGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_GetByRoleGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_ApplicationRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_GetByRoleGuid]
(

	@RoleGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[ApplicationGuid],
					[RoleGuid]
				FROM
					dbo.[Security_ApplicationRole]
				WHERE
					[RoleGuid] = @RoleGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_GetPaged]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_ApplicationRole table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[ApplicationGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [ApplicationGuid]'
				SET @SQL = @SQL + ', [RoleGuid]'
				SET @SQL = @SQL + ' FROM dbo.[Security_ApplicationRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [ApplicationGuid],'
				SET @SQL = @SQL + ' [RoleGuid]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_ApplicationRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_Insert]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_ApplicationRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_Insert]
(

	@ApplicationGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


					
				INSERT INTO dbo.[Security_ApplicationRole]
					(
					[ApplicationGuid]
					,[RoleGuid]
					)
				VALUES
					(
					@ApplicationGuid
					,@RoleGuid
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_Update]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_ApplicationRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_ApplicationRole_Update]
(

	@ApplicationGuid uniqueidentifier   ,

	@OriginalApplicationGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   ,

	@OriginalRoleGuid uniqueidentifier   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_ApplicationRole]
				SET
					[ApplicationGuid] = @ApplicationGuid
					,[RoleGuid] = @RoleGuid
				WHERE
[ApplicationGuid] = @OriginalApplicationGuid 
AND [RoleGuid] = @OriginalRoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_Delete]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_CommandRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_Delete]
(

	@CommandGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_CommandRole] WITH (ROWLOCK) 
				WHERE
					[CommandGuid] = @CommandGuid
					AND [RoleGuid] = @RoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_Find]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_CommandRole table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_Find]
(

	@SearchUsingOR bit   = null ,

	@CommandGuid uniqueidentifier   = null ,

	@RoleGuid uniqueidentifier   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [CommandGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_CommandRole]
    WHERE 
	 ([CommandGuid] = @CommandGuid OR @CommandGuid is null)
	AND ([RoleGuid] = @RoleGuid OR @RoleGuid is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [CommandGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_CommandRole]
    WHERE 
	 ([CommandGuid] = @CommandGuid AND @CommandGuid is not null)
	OR ([RoleGuid] = @RoleGuid AND @RoleGuid is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_Get_List]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_CommandRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_Get_List]

AS


				
				SELECT
					[CommandGuid],
					[RoleGuid]
				FROM
					dbo.[Security_CommandRole]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_GetByCommandGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_CommandRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_GetByCommandGuid]
(

	@CommandGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[CommandGuid],
					[RoleGuid]
				FROM
					dbo.[Security_CommandRole]
				WHERE
					[CommandGuid] = @CommandGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_GetByCommandGuidRoleGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_CommandRole table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_GetByCommandGuidRoleGuid]
(

	@CommandGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				SELECT
					[CommandGuid],
					[RoleGuid]
				FROM
					dbo.[Security_CommandRole]
				WHERE
					[CommandGuid] = @CommandGuid
					AND [RoleGuid] = @RoleGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_GetByRoleGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_CommandRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_GetByRoleGuid]
(

	@RoleGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[CommandGuid],
					[RoleGuid]
				FROM
					dbo.[Security_CommandRole]
				WHERE
					[RoleGuid] = @RoleGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_GetPaged]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_CommandRole table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[CommandGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [CommandGuid]'
				SET @SQL = @SQL + ', [RoleGuid]'
				SET @SQL = @SQL + ' FROM dbo.[Security_CommandRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [CommandGuid],'
				SET @SQL = @SQL + ' [RoleGuid]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_CommandRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_Insert]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_CommandRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_Insert]
(

	@CommandGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


					
				INSERT INTO dbo.[Security_CommandRole]
					(
					[CommandGuid]
					,[RoleGuid]
					)
				VALUES
					(
					@CommandGuid
					,@RoleGuid
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_CommandRole_Update]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_CommandRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_CommandRole_Update]
(

	@CommandGuid uniqueidentifier   ,

	@OriginalCommandGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   ,

	@OriginalRoleGuid uniqueidentifier   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_CommandRole]
				SET
					[CommandGuid] = @CommandGuid
					,[RoleGuid] = @RoleGuid
				WHERE
[CommandGuid] = @OriginalCommandGuid 
AND [RoleGuid] = @OriginalRoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_Delete]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_RibbonBarRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_Delete]
(

	@RibbonBarGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_RibbonBarRole] WITH (ROWLOCK) 
				WHERE
					[RibbonBarGuid] = @RibbonBarGuid
					AND [RoleGuid] = @RoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_Find]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_RibbonBarRole table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_Find]
(

	@SearchUsingOR bit   = null ,

	@RibbonBarGuid uniqueidentifier   = null ,

	@RoleGuid uniqueidentifier   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [RibbonBarGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_RibbonBarRole]
    WHERE 
	 ([RibbonBarGuid] = @RibbonBarGuid OR @RibbonBarGuid is null)
	AND ([RoleGuid] = @RoleGuid OR @RoleGuid is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [RibbonBarGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_RibbonBarRole]
    WHERE 
	 ([RibbonBarGuid] = @RibbonBarGuid AND @RibbonBarGuid is not null)
	OR ([RoleGuid] = @RoleGuid AND @RoleGuid is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_Get_List]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_RibbonBarRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_Get_List]

AS


				
				SELECT
					[RibbonBarGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonBarRole]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_GetByRibbonBarGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_RibbonBarRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_GetByRibbonBarGuid]
(

	@RibbonBarGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[RibbonBarGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonBarRole]
				WHERE
					[RibbonBarGuid] = @RibbonBarGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_GetByRibbonBarGuidRoleGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_RibbonBarRole table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_GetByRibbonBarGuidRoleGuid]
(

	@RibbonBarGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				SELECT
					[RibbonBarGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonBarRole]
				WHERE
					[RibbonBarGuid] = @RibbonBarGuid
					AND [RoleGuid] = @RoleGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_GetByRoleGuid]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_RibbonBarRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_GetByRoleGuid]
(

	@RoleGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[RibbonBarGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonBarRole]
				WHERE
					[RoleGuid] = @RoleGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_GetPaged]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_RibbonBarRole table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[RibbonBarGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [RibbonBarGuid]'
				SET @SQL = @SQL + ', [RoleGuid]'
				SET @SQL = @SQL + ' FROM dbo.[Security_RibbonBarRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [RibbonBarGuid],'
				SET @SQL = @SQL + ' [RoleGuid]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_RibbonBarRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_Insert]    Script Date: 07/21/2009 21:55:49 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_RibbonBarRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_Insert]
(

	@RibbonBarGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


					
				INSERT INTO dbo.[Security_RibbonBarRole]
					(
					[RibbonBarGuid]
					,[RoleGuid]
					)
				VALUES
					(
					@RibbonBarGuid
					,@RoleGuid
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonBarRole_Update]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_RibbonBarRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonBarRole_Update]
(

	@RibbonBarGuid uniqueidentifier   ,

	@OriginalRibbonBarGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   ,

	@OriginalRoleGuid uniqueidentifier   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_RibbonBarRole]
				SET
					[RibbonBarGuid] = @RibbonBarGuid
					,[RoleGuid] = @RoleGuid
				WHERE
[RibbonBarGuid] = @OriginalRibbonBarGuid 
AND [RoleGuid] = @OriginalRoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_Delete]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_RibbonRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_Delete]
(

	@RibbonGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_RibbonRole] WITH (ROWLOCK) 
				WHERE
					[RibbonGuid] = @RibbonGuid
					AND [RoleGuid] = @RoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_Find]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_RibbonRole table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_Find]
(

	@SearchUsingOR bit   = null ,

	@RibbonGuid uniqueidentifier   = null ,

	@RoleGuid uniqueidentifier   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [RibbonGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_RibbonRole]
    WHERE 
	 ([RibbonGuid] = @RibbonGuid OR @RibbonGuid is null)
	AND ([RoleGuid] = @RoleGuid OR @RoleGuid is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [RibbonGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_RibbonRole]
    WHERE 
	 ([RibbonGuid] = @RibbonGuid AND @RibbonGuid is not null)
	OR ([RoleGuid] = @RoleGuid AND @RoleGuid is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_Get_List]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_RibbonRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_Get_List]

AS


				
				SELECT
					[RibbonGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonRole]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_GetByRibbonGuid]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_RibbonRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_GetByRibbonGuid]
(

	@RibbonGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[RibbonGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonRole]
				WHERE
					[RibbonGuid] = @RibbonGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_GetByRibbonGuidRoleGuid]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_RibbonRole table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_GetByRibbonGuidRoleGuid]
(

	@RibbonGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				SELECT
					[RibbonGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonRole]
				WHERE
					[RibbonGuid] = @RibbonGuid
					AND [RoleGuid] = @RoleGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_GetByRoleGuid]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_RibbonRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_GetByRoleGuid]
(

	@RoleGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[RibbonGuid],
					[RoleGuid]
				FROM
					dbo.[Security_RibbonRole]
				WHERE
					[RoleGuid] = @RoleGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_GetPaged]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_RibbonRole table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[RibbonGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [RibbonGuid]'
				SET @SQL = @SQL + ', [RoleGuid]'
				SET @SQL = @SQL + ' FROM dbo.[Security_RibbonRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [RibbonGuid],'
				SET @SQL = @SQL + ' [RoleGuid]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_RibbonRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_Insert]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_RibbonRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_Insert]
(

	@RibbonGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


					
				INSERT INTO dbo.[Security_RibbonRole]
					(
					[RibbonGuid]
					,[RoleGuid]
					)
				VALUES
					(
					@RibbonGuid
					,@RoleGuid
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_RibbonRole_Update]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_RibbonRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_RibbonRole_Update]
(

	@RibbonGuid uniqueidentifier   ,

	@OriginalRibbonGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   ,

	@OriginalRoleGuid uniqueidentifier   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_RibbonRole]
				SET
					[RibbonGuid] = @RibbonGuid
					,[RoleGuid] = @RoleGuid
				WHERE
[RibbonGuid] = @OriginalRibbonGuid 
AND [RoleGuid] = @OriginalRoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_Delete]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_Delete]
(

	@RoleGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_Role] WITH (ROWLOCK) 
				WHERE
					[RoleGuid] = @RoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_Find]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_Role table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_Find]
(

	@SearchUsingOR bit   = null ,

	@RoleGuid uniqueidentifier   = null ,

	@RoleId int   = null ,

	@RoleName nvarchar (30)  = null ,

	@RoleDescription ntext   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [RoleGuid]
	, [RoleId]
	, [RoleName]
	, [RoleDescription]
    FROM
	dbo.[Security_Role]
    WHERE 
	 ([RoleGuid] = @RoleGuid OR @RoleGuid is null)
	AND ([RoleId] = @RoleId OR @RoleId is null)
	AND ([RoleName] = @RoleName OR @RoleName is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [RoleGuid]
	, [RoleId]
	, [RoleName]
	, [RoleDescription]
    FROM
	dbo.[Security_Role]
    WHERE 
	 ([RoleGuid] = @RoleGuid AND @RoleGuid is not null)
	OR ([RoleId] = @RoleId AND @RoleId is not null)
	OR ([RoleName] = @RoleName AND @RoleName is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_Get_List]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_Get_List]

AS


				
				SELECT
					[RoleGuid],
					[RoleId],
					[RoleName],
					[RoleDescription]
				FROM
					dbo.[Security_Role]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_GetByApplicationGuidFromSecurity_ApplicationRole]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_GetByApplicationGuidFromSecurity_ApplicationRole]
(

	@ApplicationGuid uniqueidentifier   
)
AS


SELECT dbo.[Security_Role].[RoleGuid]
       ,dbo.[Security_Role].[RoleId]
       ,dbo.[Security_Role].[RoleName]
       ,dbo.[Security_Role].[RoleDescription]
  FROM dbo.[Security_Role]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_ApplicationRole] 
                WHERE dbo.[Security_ApplicationRole].[ApplicationGuid] = @ApplicationGuid
                  AND dbo.[Security_ApplicationRole].[RoleGuid] = dbo.[Security_Role].[RoleGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_GetByCommandGuidFromSecurity_CommandRole]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_GetByCommandGuidFromSecurity_CommandRole]
(

	@CommandGuid uniqueidentifier   
)
AS


SELECT dbo.[Security_Role].[RoleGuid]
       ,dbo.[Security_Role].[RoleId]
       ,dbo.[Security_Role].[RoleName]
       ,dbo.[Security_Role].[RoleDescription]
  FROM dbo.[Security_Role]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_CommandRole] 
                WHERE dbo.[Security_CommandRole].[CommandGuid] = @CommandGuid
                  AND dbo.[Security_CommandRole].[RoleGuid] = dbo.[Security_Role].[RoleGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_GetByRibbonBarGuidFromSecurity_RibbonBarRole]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_GetByRibbonBarGuidFromSecurity_RibbonBarRole]
(

	@RibbonBarGuid uniqueidentifier   
)
AS


SELECT dbo.[Security_Role].[RoleGuid]
       ,dbo.[Security_Role].[RoleId]
       ,dbo.[Security_Role].[RoleName]
       ,dbo.[Security_Role].[RoleDescription]
  FROM dbo.[Security_Role]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_RibbonBarRole] 
                WHERE dbo.[Security_RibbonBarRole].[RibbonBarGuid] = @RibbonBarGuid
                  AND dbo.[Security_RibbonBarRole].[RoleGuid] = dbo.[Security_Role].[RoleGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_GetByRibbonGuidFromSecurity_RibbonRole]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_GetByRibbonGuidFromSecurity_RibbonRole]
(

	@RibbonGuid uniqueidentifier   
)
AS


SELECT dbo.[Security_Role].[RoleGuid]
       ,dbo.[Security_Role].[RoleId]
       ,dbo.[Security_Role].[RoleName]
       ,dbo.[Security_Role].[RoleDescription]
  FROM dbo.[Security_Role]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_RibbonRole] 
                WHERE dbo.[Security_RibbonRole].[RibbonGuid] = @RibbonGuid
                  AND dbo.[Security_RibbonRole].[RoleGuid] = dbo.[Security_Role].[RoleGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_GetByRoleGuid]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_Role table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_GetByRoleGuid]
(

	@RoleGuid uniqueidentifier   
)
AS


				SELECT
					[RoleGuid],
					[RoleId],
					[RoleName],
					[RoleDescription]
				FROM
					dbo.[Security_Role]
				WHERE
					[RoleGuid] = @RoleGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_GetByUserGuidFromSecurity_UserRole]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_GetByUserGuidFromSecurity_UserRole]
(

	@UserGuid uniqueidentifier   
)
AS


SELECT dbo.[Security_Role].[RoleGuid]
       ,dbo.[Security_Role].[RoleId]
       ,dbo.[Security_Role].[RoleName]
       ,dbo.[Security_Role].[RoleDescription]
  FROM dbo.[Security_Role]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_UserRole] 
                WHERE dbo.[Security_UserRole].[UserGuid] = @UserGuid
                  AND dbo.[Security_UserRole].[RoleGuid] = dbo.[Security_Role].[RoleGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_GetPaged]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_Role table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[RoleGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [RoleGuid]'
				SET @SQL = @SQL + ', [RoleId]'
				SET @SQL = @SQL + ', [RoleName]'
				SET @SQL = @SQL + ', [RoleDescription]'
				SET @SQL = @SQL + ' FROM dbo.[Security_Role]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [RoleGuid],'
				SET @SQL = @SQL + ' [RoleId],'
				SET @SQL = @SQL + ' [RoleName],'
				SET @SQL = @SQL + ' [RoleDescription]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_Role]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_Insert]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_Insert]
(

	@RoleGuid uniqueidentifier   ,

	@RoleId int    OUTPUT,

	@RoleName nvarchar (30)  ,

	@RoleDescription ntext   
)
AS


					
				INSERT INTO dbo.[Security_Role]
					(
					[RoleGuid]
					,[RoleName]
					,[RoleDescription]
					)
				VALUES
					(
					@RoleGuid
					,@RoleName
					,@RoleDescription
					)
				
				-- Get the identity value
				SET @RoleId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Security_Role_Update]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_Role table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Role_Update]
(

	@RoleGuid uniqueidentifier   ,

	@OriginalRoleGuid uniqueidentifier   ,

	@RoleId int   ,

	@RoleName nvarchar (30)  ,

	@RoleDescription ntext   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_Role]
				SET
					[RoleGuid] = @RoleGuid
					,[RoleName] = @RoleName
					,[RoleDescription] = @RoleDescription
				WHERE
[RoleGuid] = @OriginalRoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_Delete]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_Delete]
(

	@SessionGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_Session] WITH (ROWLOCK) 
				WHERE
					[SessionGuid] = @SessionGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_Find]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_Session table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_Find]
(

	@SearchUsingOR bit   = null ,

	@SessionGuid uniqueidentifier   = null ,

	@UserGuid uniqueidentifier   = null ,

	@ApplicationGuid uniqueidentifier   = null ,

	@ProcessDateTime datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [SessionGuid]
	, [UserGuid]
	, [ApplicationGuid]
	, [ProcessDateTime]
    FROM
	dbo.[Security_Session]
    WHERE 
	 ([SessionGuid] = @SessionGuid OR @SessionGuid is null)
	AND ([UserGuid] = @UserGuid OR @UserGuid is null)
	AND ([ApplicationGuid] = @ApplicationGuid OR @ApplicationGuid is null)
	AND ([ProcessDateTime] = @ProcessDateTime OR @ProcessDateTime is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [SessionGuid]
	, [UserGuid]
	, [ApplicationGuid]
	, [ProcessDateTime]
    FROM
	dbo.[Security_Session]
    WHERE 
	 ([SessionGuid] = @SessionGuid AND @SessionGuid is not null)
	OR ([UserGuid] = @UserGuid AND @UserGuid is not null)
	OR ([ApplicationGuid] = @ApplicationGuid AND @ApplicationGuid is not null)
	OR ([ProcessDateTime] = @ProcessDateTime AND @ProcessDateTime is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_Get_List]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_Get_List]

AS


				
				SELECT
					[SessionGuid],
					[UserGuid],
					[ApplicationGuid],
					[ProcessDateTime]
				FROM
					dbo.[Security_Session]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_GetBySessionGuid]    Script Date: 07/21/2009 21:55:50 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_Session table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_GetBySessionGuid]
(

	@SessionGuid uniqueidentifier   
)
AS


				SELECT
					[SessionGuid],
					[UserGuid],
					[ApplicationGuid],
					[ProcessDateTime]
				FROM
					dbo.[Security_Session]
				WHERE
					[SessionGuid] = @SessionGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_GetByUserGuid]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_Session table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_GetByUserGuid]
(

	@UserGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[SessionGuid],
					[UserGuid],
					[ApplicationGuid],
					[ProcessDateTime]
				FROM
					dbo.[Security_Session]
				WHERE
					[UserGuid] = @UserGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_GetPaged]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_Session table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[SessionGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [SessionGuid]'
				SET @SQL = @SQL + ', [UserGuid]'
				SET @SQL = @SQL + ', [ApplicationGuid]'
				SET @SQL = @SQL + ', [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM dbo.[Security_Session]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [SessionGuid],'
				SET @SQL = @SQL + ' [UserGuid],'
				SET @SQL = @SQL + ' [ApplicationGuid],'
				SET @SQL = @SQL + ' [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_Session]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_Insert]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_Insert]
(

	@SessionGuid uniqueidentifier   ,

	@UserGuid uniqueidentifier   ,

	@ApplicationGuid uniqueidentifier   ,

	@ProcessDateTime datetime   
)
AS


					
				INSERT INTO dbo.[Security_Session]
					(
					[SessionGuid]
					,[UserGuid]
					,[ApplicationGuid]
					,[ProcessDateTime]
					)
				VALUES
					(
					@SessionGuid
					,@UserGuid
					,@ApplicationGuid
					,@ProcessDateTime
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_Session_Update]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Session_Update]
(

	@SessionGuid uniqueidentifier   ,

	@OriginalSessionGuid uniqueidentifier   ,

	@UserGuid uniqueidentifier   ,

	@ApplicationGuid uniqueidentifier   ,

	@ProcessDateTime datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_Session]
				SET
					[SessionGuid] = @SessionGuid
					,[UserGuid] = @UserGuid
					,[ApplicationGuid] = @ApplicationGuid
					,[ProcessDateTime] = @ProcessDateTime
				WHERE
[SessionGuid] = @OriginalSessionGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_Delete]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_SessionActivity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_Delete]
(

	@SessionActivityGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_SessionActivity] WITH (ROWLOCK) 
				WHERE
					[SessionActivityGuid] = @SessionActivityGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_Find]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_SessionActivity table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_Find]
(

	@SearchUsingOR bit   = null ,

	@SessionActivityGuid uniqueidentifier   = null ,

	@SessionGuid uniqueidentifier   = null ,

	@ProcessDateTime datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [SessionActivityGuid]
	, [SessionGuid]
	, [ProcessDateTime]
    FROM
	dbo.[Security_SessionActivity]
    WHERE 
	 ([SessionActivityGuid] = @SessionActivityGuid OR @SessionActivityGuid is null)
	AND ([SessionGuid] = @SessionGuid OR @SessionGuid is null)
	AND ([ProcessDateTime] = @ProcessDateTime OR @ProcessDateTime is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [SessionActivityGuid]
	, [SessionGuid]
	, [ProcessDateTime]
    FROM
	dbo.[Security_SessionActivity]
    WHERE 
	 ([SessionActivityGuid] = @SessionActivityGuid AND @SessionActivityGuid is not null)
	OR ([SessionGuid] = @SessionGuid AND @SessionGuid is not null)
	OR ([ProcessDateTime] = @ProcessDateTime AND @ProcessDateTime is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_Get_List]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_SessionActivity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_Get_List]

AS


				
				SELECT
					[SessionActivityGuid],
					[SessionGuid],
					[ProcessDateTime]
				FROM
					dbo.[Security_SessionActivity]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_GetBySessionActivityGuid]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_SessionActivity table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_GetBySessionActivityGuid]
(

	@SessionActivityGuid uniqueidentifier   
)
AS


				SELECT
					[SessionActivityGuid],
					[SessionGuid],
					[ProcessDateTime]
				FROM
					dbo.[Security_SessionActivity]
				WHERE
					[SessionActivityGuid] = @SessionActivityGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_GetBySessionGuid]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_SessionActivity table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_GetBySessionGuid]
(

	@SessionGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[SessionActivityGuid],
					[SessionGuid],
					[ProcessDateTime]
				FROM
					dbo.[Security_SessionActivity]
				WHERE
					[SessionGuid] = @SessionGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_GetPaged]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_SessionActivity table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[SessionActivityGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [SessionActivityGuid]'
				SET @SQL = @SQL + ', [SessionGuid]'
				SET @SQL = @SQL + ', [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM dbo.[Security_SessionActivity]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [SessionActivityGuid],'
				SET @SQL = @SQL + ' [SessionGuid],'
				SET @SQL = @SQL + ' [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_SessionActivity]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_Insert]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_SessionActivity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_Insert]
(

	@SessionActivityGuid uniqueidentifier   ,

	@SessionGuid uniqueidentifier   ,

	@ProcessDateTime datetime   
)
AS


					
				INSERT INTO dbo.[Security_SessionActivity]
					(
					[SessionActivityGuid]
					,[SessionGuid]
					,[ProcessDateTime]
					)
				VALUES
					(
					@SessionActivityGuid
					,@SessionGuid
					,@ProcessDateTime
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_SessionActivity_Update]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_SessionActivity table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_SessionActivity_Update]
(

	@SessionActivityGuid uniqueidentifier   ,

	@OriginalSessionActivityGuid uniqueidentifier   ,

	@SessionGuid uniqueidentifier   ,

	@ProcessDateTime datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_SessionActivity]
				SET
					[SessionActivityGuid] = @SessionActivityGuid
					,[SessionGuid] = @SessionGuid
					,[ProcessDateTime] = @ProcessDateTime
				WHERE
[SessionActivityGuid] = @OriginalSessionActivityGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_Transaction_Delete]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_Transaction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Transaction_Delete]
(

	@TransactionGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_Transaction] WITH (ROWLOCK) 
				WHERE
					[TransactionGuid] = @TransactionGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_Transaction_Find]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_Transaction table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Transaction_Find]
(

	@SearchUsingOR bit   = null ,

	@TransactionGuid uniqueidentifier   = null ,

	@SessionGuid uniqueidentifier   = null ,

	@TransactionTypeGuid uniqueidentifier   = null ,

	@Completed bit   = null ,

	@CompletedDateTime datetime   = null ,

	@ProcessDateTime datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [TransactionGuid]
	, [SessionGuid]
	, [TransactionTypeGuid]
	, [Completed]
	, [CompletedDateTime]
	, [ProcessDateTime]
    FROM
	dbo.[Security_Transaction]
    WHERE 
	 ([TransactionGuid] = @TransactionGuid OR @TransactionGuid is null)
	AND ([SessionGuid] = @SessionGuid OR @SessionGuid is null)
	AND ([TransactionTypeGuid] = @TransactionTypeGuid OR @TransactionTypeGuid is null)
	AND ([Completed] = @Completed OR @Completed is null)
	AND ([CompletedDateTime] = @CompletedDateTime OR @CompletedDateTime is null)
	AND ([ProcessDateTime] = @ProcessDateTime OR @ProcessDateTime is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [TransactionGuid]
	, [SessionGuid]
	, [TransactionTypeGuid]
	, [Completed]
	, [CompletedDateTime]
	, [ProcessDateTime]
    FROM
	dbo.[Security_Transaction]
    WHERE 
	 ([TransactionGuid] = @TransactionGuid AND @TransactionGuid is not null)
	OR ([SessionGuid] = @SessionGuid AND @SessionGuid is not null)
	OR ([TransactionTypeGuid] = @TransactionTypeGuid AND @TransactionTypeGuid is not null)
	OR ([Completed] = @Completed AND @Completed is not null)
	OR ([CompletedDateTime] = @CompletedDateTime AND @CompletedDateTime is not null)
	OR ([ProcessDateTime] = @ProcessDateTime AND @ProcessDateTime is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_Transaction_Get_List]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_Transaction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Transaction_Get_List]

AS


				
				SELECT
					[TransactionGuid],
					[SessionGuid],
					[TransactionTypeGuid],
					[Completed],
					[CompletedDateTime],
					[ProcessDateTime]
				FROM
					dbo.[Security_Transaction]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Transaction_GetByTransactionGuid]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_Transaction table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Transaction_GetByTransactionGuid]
(

	@TransactionGuid uniqueidentifier   
)
AS


				SELECT
					[TransactionGuid],
					[SessionGuid],
					[TransactionTypeGuid],
					[Completed],
					[CompletedDateTime],
					[ProcessDateTime]
				FROM
					dbo.[Security_Transaction]
				WHERE
					[TransactionGuid] = @TransactionGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_Transaction_GetPaged]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_Transaction table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Transaction_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[TransactionGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [TransactionGuid]'
				SET @SQL = @SQL + ', [SessionGuid]'
				SET @SQL = @SQL + ', [TransactionTypeGuid]'
				SET @SQL = @SQL + ', [Completed]'
				SET @SQL = @SQL + ', [CompletedDateTime]'
				SET @SQL = @SQL + ', [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM dbo.[Security_Transaction]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [TransactionGuid],'
				SET @SQL = @SQL + ' [SessionGuid],'
				SET @SQL = @SQL + ' [TransactionTypeGuid],'
				SET @SQL = @SQL + ' [Completed],'
				SET @SQL = @SQL + ' [CompletedDateTime],'
				SET @SQL = @SQL + ' [ProcessDateTime]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_Transaction]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_Transaction_Insert]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_Transaction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Transaction_Insert]
(

	@TransactionGuid uniqueidentifier   ,

	@SessionGuid uniqueidentifier   ,

	@TransactionTypeGuid uniqueidentifier   ,

	@Completed bit   ,

	@CompletedDateTime datetime   ,

	@ProcessDateTime datetime   
)
AS


					
				INSERT INTO dbo.[Security_Transaction]
					(
					[TransactionGuid]
					,[SessionGuid]
					,[TransactionTypeGuid]
					,[Completed]
					,[CompletedDateTime]
					,[ProcessDateTime]
					)
				VALUES
					(
					@TransactionGuid
					,@SessionGuid
					,@TransactionTypeGuid
					,@Completed
					,@CompletedDateTime
					,@ProcessDateTime
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_Transaction_Update]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_Transaction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_Transaction_Update]
(

	@TransactionGuid uniqueidentifier   ,

	@OriginalTransactionGuid uniqueidentifier   ,

	@SessionGuid uniqueidentifier   ,

	@TransactionTypeGuid uniqueidentifier   ,

	@Completed bit   ,

	@CompletedDateTime datetime   ,

	@ProcessDateTime datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_Transaction]
				SET
					[TransactionGuid] = @TransactionGuid
					,[SessionGuid] = @SessionGuid
					,[TransactionTypeGuid] = @TransactionTypeGuid
					,[Completed] = @Completed
					,[CompletedDateTime] = @CompletedDateTime
					,[ProcessDateTime] = @ProcessDateTime
				WHERE
[TransactionGuid] = @OriginalTransactionGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_User_Delete]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_User table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_User_Delete]
(

	@UserGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_User] WITH (ROWLOCK) 
				WHERE
					[UserGuid] = @UserGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_User_Find]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_User table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_User_Find]
(

	@SearchUsingOR bit   = null ,

	@UserGuid uniqueidentifier   = null ,

	@UserId int   = null ,

	@Username nvarchar (50)  = null ,

	@Password nvarchar (255)  = null ,

	@Email nvarchar (128)  = null ,

	@Active bit   = null ,

	@StartupScript nvarchar (255)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [UserGuid]
	, [UserId]
	, [Username]
	, [Password]
	, [email]
	, [Active]
	, [StartupScript]
    FROM
	dbo.[Security_User]
    WHERE 
	 ([UserGuid] = @UserGuid OR @UserGuid is null)
	AND ([UserId] = @UserId OR @UserId is null)
	AND ([Username] = @Username OR @Username is null)
	AND ([Password] = @Password OR @Password is null)
	AND ([email] = @Email OR @Email is null)
	AND ([Active] = @Active OR @Active is null)
	AND ([StartupScript] = @StartupScript OR @StartupScript is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [UserGuid]
	, [UserId]
	, [Username]
	, [Password]
	, [email]
	, [Active]
	, [StartupScript]
    FROM
	dbo.[Security_User]
    WHERE 
	 ([UserGuid] = @UserGuid AND @UserGuid is not null)
	OR ([UserId] = @UserId AND @UserId is not null)
	OR ([Username] = @Username AND @Username is not null)
	OR ([Password] = @Password AND @Password is not null)
	OR ([email] = @Email AND @Email is not null)
	OR ([Active] = @Active AND @Active is not null)
	OR ([StartupScript] = @StartupScript AND @StartupScript is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_User_Get_List]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_User table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_User_Get_List]

AS


				
				SELECT
					[UserGuid],
					[UserId],
					[Username],
					[Password],
					[email],
					[Active],
					[StartupScript]
				FROM
					dbo.[Security_User]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_User_GetByRoleGuidFromSecurity_UserRole]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_User_GetByRoleGuidFromSecurity_UserRole]
(

	@RoleGuid uniqueidentifier   
)
AS


SELECT dbo.[Security_User].[UserGuid]
       ,dbo.[Security_User].[UserId]
       ,dbo.[Security_User].[Username]
       ,dbo.[Security_User].[Password]
       ,dbo.[Security_User].[email]
       ,dbo.[Security_User].[Active]
       ,dbo.[Security_User].[StartupScript]
  FROM dbo.[Security_User]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[Security_UserRole] 
                WHERE dbo.[Security_UserRole].[RoleGuid] = @RoleGuid
                  AND dbo.[Security_UserRole].[UserGuid] = dbo.[Security_User].[UserGuid]
                  )
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_User_GetByUserGuid]    Script Date: 07/21/2009 21:55:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_User table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_User_GetByUserGuid]
(

	@UserGuid uniqueidentifier   
)
AS


				SELECT
					[UserGuid],
					[UserId],
					[Username],
					[Password],
					[email],
					[Active],
					[StartupScript]
				FROM
					dbo.[Security_User]
				WHERE
					[UserGuid] = @UserGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_User_GetPaged]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_User table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_User_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[UserGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [UserGuid]'
				SET @SQL = @SQL + ', [UserId]'
				SET @SQL = @SQL + ', [Username]'
				SET @SQL = @SQL + ', [Password]'
				SET @SQL = @SQL + ', [email]'
				SET @SQL = @SQL + ', [Active]'
				SET @SQL = @SQL + ', [StartupScript]'
				SET @SQL = @SQL + ' FROM dbo.[Security_User]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [UserGuid],'
				SET @SQL = @SQL + ' [UserId],'
				SET @SQL = @SQL + ' [Username],'
				SET @SQL = @SQL + ' [Password],'
				SET @SQL = @SQL + ' [email],'
				SET @SQL = @SQL + ' [Active],'
				SET @SQL = @SQL + ' [StartupScript]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_User]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_User_Insert]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_User table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_User_Insert]
(

	@UserGuid uniqueidentifier   ,

	@UserId int    OUTPUT,

	@Username nvarchar (50)  ,

	@Password nvarchar (255)  ,

	@Email nvarchar (128)  ,

	@Active bit   ,

	@StartupScript nvarchar (255)  
)
AS


					
				INSERT INTO dbo.[Security_User]
					(
					[UserGuid]
					,[Username]
					,[Password]
					,[email]
					,[Active]
					,[StartupScript]
					)
				VALUES
					(
					@UserGuid
					,@Username
					,@Password
					,@Email
					,@Active
					,@StartupScript
					)
				
				-- Get the identity value
				SET @UserId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Security_User_Update]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Security_User_Update]
(

	@UserGuid uniqueidentifier   ,

	@OriginalUserGuid uniqueidentifier   ,

	@UserId int   ,

	@Username nvarchar (50)  ,

	@Password nvarchar (255)  ,

	@Email nvarchar (128)  ,

	@Active bit   ,

	@StartupScript nvarchar (255)  
)
AS

IF (LEN(@Password) = 0)
BEGIN
	-- Modify the updatable columns
	UPDATE
		dbo.[Security_User]
	SET
		[UserGuid] = @UserGuid
		,[Username] = @Username
		,[email] = @Email
		,[Active] = @Active
		,[StartupScript] = @StartupScript
	WHERE
		[UserGuid] = @OriginalUserGuid
END
ELSE BEGIN
	-- Modify the updatable columns
	UPDATE
		dbo.[Security_User]
	SET
		[UserGuid] = @UserGuid
		,[Username] = @Username
		,[Password] = @Password
		,[email] = @Email
		,[Active] = @Active
		,[StartupScript] = @StartupScript
	WHERE
		[UserGuid] = @OriginalUserGuid
END

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_Delete]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Security_UserRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_Delete]
(

	@UserGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Security_UserRole] WITH (ROWLOCK) 
				WHERE
					[UserGuid] = @UserGuid
					AND [RoleGuid] = @RoleGuid

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_Find]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Security_UserRole table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_Find]
(

	@SearchUsingOR bit   = null ,

	@UserGuid uniqueidentifier   = null ,

	@RoleGuid uniqueidentifier   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [UserGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_UserRole]
    WHERE 
	 ([UserGuid] = @UserGuid OR @UserGuid is null)
	AND ([RoleGuid] = @RoleGuid OR @RoleGuid is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [UserGuid]
	, [RoleGuid]
    FROM
	dbo.[Security_UserRole]
    WHERE 
	 ([UserGuid] = @UserGuid AND @UserGuid is not null)
	OR ([RoleGuid] = @RoleGuid AND @RoleGuid is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_Get_List]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Security_UserRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_Get_List]

AS


				
				SELECT
					[UserGuid],
					[RoleGuid]
				FROM
					dbo.[Security_UserRole]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_GetByRoleGuid]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_UserRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_GetByRoleGuid]
(

	@RoleGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[UserGuid],
					[RoleGuid]
				FROM
					dbo.[Security_UserRole]
				WHERE
					[RoleGuid] = @RoleGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_GetByUserGuid]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_UserRole table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_GetByUserGuid]
(

	@UserGuid uniqueidentifier   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[UserGuid],
					[RoleGuid]
				FROM
					dbo.[Security_UserRole]
				WHERE
					[UserGuid] = @UserGuid
				
				Select @@ROWCOUNT
				SET ANSI_NULLS ON

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_GetByUserGuidRoleGuid]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Security_UserRole table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_GetByUserGuidRoleGuid]
(

	@UserGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


				SELECT
					[UserGuid],
					[RoleGuid]
				FROM
					dbo.[Security_UserRole]
				WHERE
					[UserGuid] = @UserGuid
					AND [RoleGuid] = @RoleGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_GetPaged]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Security_UserRole table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[UserGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [UserGuid]'
				SET @SQL = @SQL + ', [RoleGuid]'
				SET @SQL = @SQL + ' FROM dbo.[Security_UserRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [UserGuid],'
				SET @SQL = @SQL + ' [RoleGuid]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Security_UserRole]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_Insert]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Security_UserRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_Insert]
(

	@UserGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   
)
AS


					
				INSERT INTO dbo.[Security_UserRole]
					(
					[UserGuid]
					,[RoleGuid]
					)
				VALUES
					(
					@UserGuid
					,@RoleGuid
					)

GO

/****** Object:  StoredProcedure [dbo].[Security_UserRole_Update]    Script Date: 07/21/2009 21:55:52 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Security_UserRole table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Security_UserRole_Update]
(

	@UserGuid uniqueidentifier   ,

	@OriginalUserGuid uniqueidentifier   ,

	@RoleGuid uniqueidentifier   ,

	@OriginalRoleGuid uniqueidentifier   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Security_UserRole]
				SET
					[UserGuid] = @UserGuid
					,[RoleGuid] = @RoleGuid
				WHERE
[UserGuid] = @OriginalUserGuid 
AND [RoleGuid] = @OriginalRoleGuid

GO

/****** Object:  StoredProcedure [dbo].[SelectManyLookup]    Script Date: 07/21/2009 21:55:52 ******/
CREATE PROCEDURE [dbo].[SelectManyLookup]
	@TableName [nvarchar](4000),
	@ValueColumnName [nvarchar](4000),
	@DisplayColumnName [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[SelectManyLookup]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyLookup'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\SelectManyLookup.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyLookup'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyLookup'
GO

/****** Object:  StoredProcedure [dbo].[SelectManyModule_Application]    Script Date: 07/21/2009 21:55:52 ******/
CREATE PROCEDURE [dbo].[SelectManyModule_Application]
	@Username [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[SelectManyModule_Application]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Application'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\SelectManyModule_Application.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Application'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Application'
GO

/****** Object:  StoredProcedure [dbo].[SelectManyModule_BarItem]    Script Date: 07/21/2009 21:55:52 ******/
CREATE PROCEDURE [dbo].[SelectManyModule_BarItem]
	@SessionGuid [uniqueidentifier],
	@RibbonBarGuid [uniqueidentifier],
	@ParentBarItemGuid [uniqueidentifier]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[SelectManyModule_BarItem]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_BarItem'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\SelectManyModule_BarItem.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_BarItem'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_BarItem'
GO

/****** Object:  StoredProcedure [dbo].[SelectManyModule_Command]    Script Date: 07/21/2009 21:55:52 ******/
CREATE PROCEDURE [dbo].[SelectManyModule_Command]
	@SessionGuid [uniqueidentifier],
	@FileGuid [uniqueidentifier]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[SelectManyModule_Command]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Command'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\SelectManyModule_Command.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Command'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Command'
GO

/****** Object:  StoredProcedure [dbo].[SelectManyModule_File]    Script Date: 07/21/2009 21:55:52 ******/
CREATE PROCEDURE [dbo].[SelectManyModule_File]
	@SessionGuid [uniqueidentifier],
	@ApplicationGuid [uniqueidentifier]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[SelectManyModule_File]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_File'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\SelectManyModule_File.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_File'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_File'
GO

/****** Object:  StoredProcedure [dbo].[SelectManyModule_Ribbon]    Script Date: 07/21/2009 21:55:52 ******/
CREATE PROCEDURE [dbo].[SelectManyModule_Ribbon]
	@SessionGuid [uniqueidentifier]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[SelectManyModule_Ribbon]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Ribbon'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\SelectManyModule_Ribbon.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Ribbon'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_Ribbon'
GO

/****** Object:  StoredProcedure [dbo].[SelectManyModule_RibbonBar]    Script Date: 07/21/2009 21:55:53 ******/
CREATE PROCEDURE [dbo].[SelectManyModule_RibbonBar]
	@SessionGuid [uniqueidentifier],
	@RibbonGuid [uniqueidentifier]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GenesisDbClr].[StoredProcedures].[SelectManyModule_RibbonBar]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_RibbonBar'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Stored Procedures\SelectManyModule_RibbonBar.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_RibbonBar'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SelectManyModule_RibbonBar'
GO

/****** Object:  StoredProcedure [dbo].[SelectSimpleLookup]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[SelectSimpleLookup]
(	
	@TableName	VARCHAR(30)
)
AS

--DECLARE @TableName VARCHAR(30)
--SET @TableName = 'Title'

DECLARE @DynamicSQL VARCHAR(1000)

SET @DynamicSQL = 'SELECT ' + @TableName + 'Id AS ''Id'', ' + @TableName + 'Code AS ''Code'', ' + @TableName + 'Name AS ''Name'' FROM Lookup.' + @TableName

EXEC(@DynamicSQL)

GO

/****** Object:  StoredProcedure [dbo].[Transaction_Category_Delete]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Transaction_Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_Category_Delete]
(

	@TransactionCategoryGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Transaction_Category] WITH (ROWLOCK) 
				WHERE
					[TransactionCategoryGuid] = @TransactionCategoryGuid

GO

/****** Object:  StoredProcedure [dbo].[Transaction_Category_Find]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Transaction_Category table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_Category_Find]
(

	@SearchUsingOR bit   = null ,

	@TransactionCategoryGuid uniqueidentifier   = null ,

	@TransactionCategoryId int   = null ,

	@ParentTransactionCategoryGuid uniqueidentifier   = null ,

	@TransactionCategoryName nvarchar (50)  = null ,

	@TransactionCategoryDescription ntext   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [TransactionCategoryGuid]
	, [TransactionCategoryId]
	, [ParentTransactionCategoryGuid]
	, [TransactionCategoryName]
	, [TransactionCategoryDescription]
    FROM
	dbo.[Transaction_Category]
    WHERE 
	 ([TransactionCategoryGuid] = @TransactionCategoryGuid OR @TransactionCategoryGuid is null)
	AND ([TransactionCategoryId] = @TransactionCategoryId OR @TransactionCategoryId is null)
	AND ([ParentTransactionCategoryGuid] = @ParentTransactionCategoryGuid OR @ParentTransactionCategoryGuid is null)
	AND ([TransactionCategoryName] = @TransactionCategoryName OR @TransactionCategoryName is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [TransactionCategoryGuid]
	, [TransactionCategoryId]
	, [ParentTransactionCategoryGuid]
	, [TransactionCategoryName]
	, [TransactionCategoryDescription]
    FROM
	dbo.[Transaction_Category]
    WHERE 
	 ([TransactionCategoryGuid] = @TransactionCategoryGuid AND @TransactionCategoryGuid is not null)
	OR ([TransactionCategoryId] = @TransactionCategoryId AND @TransactionCategoryId is not null)
	OR ([ParentTransactionCategoryGuid] = @ParentTransactionCategoryGuid AND @ParentTransactionCategoryGuid is not null)
	OR ([TransactionCategoryName] = @TransactionCategoryName AND @TransactionCategoryName is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Transaction_Category_Get_List]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Transaction_Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_Category_Get_List]

AS


				
				SELECT
					[TransactionCategoryGuid],
					[TransactionCategoryId],
					[ParentTransactionCategoryGuid],
					[TransactionCategoryName],
					[TransactionCategoryDescription]
				FROM
					dbo.[Transaction_Category]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Transaction_Category_GetByTransactionCategoryGuid]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Transaction_Category table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_Category_GetByTransactionCategoryGuid]
(

	@TransactionCategoryGuid uniqueidentifier   
)
AS


				SELECT
					[TransactionCategoryGuid],
					[TransactionCategoryId],
					[ParentTransactionCategoryGuid],
					[TransactionCategoryName],
					[TransactionCategoryDescription]
				FROM
					dbo.[Transaction_Category]
				WHERE
					[TransactionCategoryGuid] = @TransactionCategoryGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Transaction_Category_GetPaged]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Transaction_Category table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_Category_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[TransactionCategoryGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [TransactionCategoryGuid]'
				SET @SQL = @SQL + ', [TransactionCategoryId]'
				SET @SQL = @SQL + ', [ParentTransactionCategoryGuid]'
				SET @SQL = @SQL + ', [TransactionCategoryName]'
				SET @SQL = @SQL + ', [TransactionCategoryDescription]'
				SET @SQL = @SQL + ' FROM dbo.[Transaction_Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [TransactionCategoryGuid],'
				SET @SQL = @SQL + ' [TransactionCategoryId],'
				SET @SQL = @SQL + ' [ParentTransactionCategoryGuid],'
				SET @SQL = @SQL + ' [TransactionCategoryName],'
				SET @SQL = @SQL + ' [TransactionCategoryDescription]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Transaction_Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Transaction_Category_Insert]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Transaction_Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_Category_Insert]
(

	@TransactionCategoryGuid uniqueidentifier   ,

	@TransactionCategoryId int    OUTPUT,

	@ParentTransactionCategoryGuid uniqueidentifier   ,

	@TransactionCategoryName nvarchar (50)  ,

	@TransactionCategoryDescription ntext   
)
AS


					
				INSERT INTO dbo.[Transaction_Category]
					(
					[TransactionCategoryGuid]
					,[ParentTransactionCategoryGuid]
					,[TransactionCategoryName]
					,[TransactionCategoryDescription]
					)
				VALUES
					(
					@TransactionCategoryGuid
					,@ParentTransactionCategoryGuid
					,@TransactionCategoryName
					,@TransactionCategoryDescription
					)
				
				-- Get the identity value
				SET @TransactionCategoryId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Transaction_Category_Update]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Transaction_Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_Category_Update]
(

	@TransactionCategoryGuid uniqueidentifier   ,

	@OriginalTransactionCategoryGuid uniqueidentifier   ,

	@TransactionCategoryId int   ,

	@ParentTransactionCategoryGuid uniqueidentifier   ,

	@TransactionCategoryName nvarchar (50)  ,

	@TransactionCategoryDescription ntext   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Transaction_Category]
				SET
					[TransactionCategoryGuid] = @TransactionCategoryGuid
					,[ParentTransactionCategoryGuid] = @ParentTransactionCategoryGuid
					,[TransactionCategoryName] = @TransactionCategoryName
					,[TransactionCategoryDescription] = @TransactionCategoryDescription
				WHERE
[TransactionCategoryGuid] = @OriginalTransactionCategoryGuid

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_Delete]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Deletes a record in the Transaction_TransactionType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_Delete]
(

	@TransactionTypeGuid uniqueidentifier   
)
AS


				DELETE FROM dbo.[Transaction_TransactionType] WITH (ROWLOCK) 
				WHERE
					[TransactionTypeGuid] = @TransactionTypeGuid

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_Find]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Finds records in the Transaction_TransactionType table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_Find]
(

	@SearchUsingOR bit   = null ,

	@TransactionTypeGuid uniqueidentifier   = null ,

	@TransactionTypeId int   = null ,

	@TransactionCategoryGuid uniqueidentifier   = null ,

	@TransactionTypeName nvarchar (30)  = null ,

	@TransactionTypeCode nvarchar (128)  = null ,

	@TransactionTypeDescription ntext   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [TransactionTypeGuid]
	, [TransactionTypeId]
	, [TransactionCategoryGuid]
	, [TransactionTypeName]
	, [TransactionTypeCode]
	, [TransactionTypeDescription]
    FROM
	dbo.[Transaction_TransactionType]
    WHERE 
	 ([TransactionTypeGuid] = @TransactionTypeGuid OR @TransactionTypeGuid is null)
	AND ([TransactionTypeId] = @TransactionTypeId OR @TransactionTypeId is null)
	AND ([TransactionCategoryGuid] = @TransactionCategoryGuid OR @TransactionCategoryGuid is null)
	AND ([TransactionTypeName] = @TransactionTypeName OR @TransactionTypeName is null)
	AND ([TransactionTypeCode] = @TransactionTypeCode OR @TransactionTypeCode is null)
						
  END
  ELSE
  BEGIN
    SELECT
	  [TransactionTypeGuid]
	, [TransactionTypeId]
	, [TransactionCategoryGuid]
	, [TransactionTypeName]
	, [TransactionTypeCode]
	, [TransactionTypeDescription]
    FROM
	dbo.[Transaction_TransactionType]
    WHERE 
	 ([TransactionTypeGuid] = @TransactionTypeGuid AND @TransactionTypeGuid is not null)
	OR ([TransactionTypeId] = @TransactionTypeId AND @TransactionTypeId is not null)
	OR ([TransactionCategoryGuid] = @TransactionCategoryGuid AND @TransactionCategoryGuid is not null)
	OR ([TransactionTypeName] = @TransactionTypeName AND @TransactionTypeName is not null)
	OR ([TransactionTypeCode] = @TransactionTypeCode AND @TransactionTypeCode is not null)
	Select @@ROWCOUNT			
  END

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_Get_List]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the Transaction_TransactionType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_Get_List]

AS


				
				SELECT
					[TransactionTypeGuid],
					[TransactionTypeId],
					[TransactionCategoryGuid],
					[TransactionTypeName],
					[TransactionTypeCode],
					[TransactionTypeDescription]
				FROM
					dbo.[Transaction_TransactionType]
					
				Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_GetByTransactionTypeCode]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Transaction_TransactionType table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_GetByTransactionTypeCode]
(

	@TransactionTypeCode nvarchar (128)  
)
AS


				SELECT
					[TransactionTypeGuid],
					[TransactionTypeId],
					[TransactionCategoryGuid],
					[TransactionTypeName],
					[TransactionTypeCode],
					[TransactionTypeDescription]
				FROM
					dbo.[Transaction_TransactionType]
				WHERE
					[TransactionTypeCode] = @TransactionTypeCode
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_GetByTransactionTypeGuid]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Select records from the Transaction_TransactionType table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_GetByTransactionTypeGuid]
(

	@TransactionTypeGuid uniqueidentifier   
)
AS


				SELECT
					[TransactionTypeGuid],
					[TransactionTypeId],
					[TransactionCategoryGuid],
					[TransactionTypeName],
					[TransactionTypeCode],
					[TransactionTypeDescription]
				FROM
					dbo.[Transaction_TransactionType]
				WHERE
					[TransactionTypeGuid] = @TransactionTypeGuid
			Select @@ROWCOUNT

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_GetPaged]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the Transaction_TransactionType table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_GetPaged]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[TransactionTypeGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [TransactionTypeGuid]'
				SET @SQL = @SQL + ', [TransactionTypeId]'
				SET @SQL = @SQL + ', [TransactionCategoryGuid]'
				SET @SQL = @SQL + ', [TransactionTypeName]'
				SET @SQL = @SQL + ', [TransactionTypeCode]'
				SET @SQL = @SQL + ', [TransactionTypeDescription]'
				SET @SQL = @SQL + ' FROM dbo.[Transaction_TransactionType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [TransactionTypeGuid],'
				SET @SQL = @SQL + ' [TransactionTypeId],'
				SET @SQL = @SQL + ' [TransactionCategoryGuid],'
				SET @SQL = @SQL + ' [TransactionTypeName],'
				SET @SQL = @SQL + ' [TransactionTypeCode],'
				SET @SQL = @SQL + ' [TransactionTypeDescription]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[Transaction_TransactionType]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
			
				END

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_Insert]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Inserts a record into the Transaction_TransactionType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_Insert]
(

	@TransactionTypeGuid uniqueidentifier   ,

	@TransactionTypeId int    OUTPUT,

	@TransactionCategoryGuid uniqueidentifier   ,

	@TransactionTypeName nvarchar (30)  ,

	@TransactionTypeCode nvarchar (128)  ,

	@TransactionTypeDescription ntext   
)
AS


					
				INSERT INTO dbo.[Transaction_TransactionType]
					(
					[TransactionTypeGuid]
					,[TransactionCategoryGuid]
					,[TransactionTypeName]
					,[TransactionTypeCode]
					,[TransactionTypeDescription]
					)
				VALUES
					(
					@TransactionTypeGuid
					,@TransactionCategoryGuid
					,@TransactionTypeName
					,@TransactionTypeCode
					,@TransactionTypeDescription
					)
				
				-- Get the identity value
				SET @TransactionTypeId = SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[Transaction_TransactionType_Update]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Updates a record in the Transaction_TransactionType table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[Transaction_TransactionType_Update]
(

	@TransactionTypeGuid uniqueidentifier   ,

	@OriginalTransactionTypeGuid uniqueidentifier   ,

	@TransactionTypeId int   ,

	@TransactionCategoryGuid uniqueidentifier   ,

	@TransactionTypeName nvarchar (30)  ,

	@TransactionTypeCode nvarchar (128)  ,

	@TransactionTypeDescription ntext   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					dbo.[Transaction_TransactionType]
				SET
					[TransactionTypeGuid] = @TransactionTypeGuid
					,[TransactionCategoryGuid] = @TransactionCategoryGuid
					,[TransactionTypeName] = @TransactionTypeName
					,[TransactionTypeCode] = @TransactionTypeCode
					,[TransactionTypeDescription] = @TransactionTypeDescription
				WHERE
[TransactionTypeGuid] = @OriginalTransactionTypeGuid

GO

/****** Object:  StoredProcedure [dbo].[Update_Module_Application]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Update_Module_Application]

(		    @Applicationguid uniqueidentifier
		   ,@ApplicationName nvarchar(50)
           ,@ApplicationDescription ntext
           ,@ApplicationOrder int
           ,@ApplicationVisible bit
)

as
UPDATE [Genesis].[dbo].[Module_Application]
SET
            [ApplicationName] = @ApplicationName
           ,[ApplicationDescription] = @ApplicationDescription
           ,[ApplicationOrder] = @ApplicationOrder
           ,[ApplicationVisible] = @ApplicationVisible
          

WHERE
Applicationguid = @Applicationguid

GO

/****** Object:  StoredProcedure [dbo].[Update_Module_BarItem]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Update_Module_BarItem]
			@BarItemGuid uniqueidentifier
           ,@ParentBarItemGuid uniqueidentifier
           ,@RibbonBarGuid uniqueidentifier
           ,@CommandGuid uniqueidentifier
           ,@BarItemName nvarchar(128)
           ,@BarItemImage nvarchar(1024)
           ,@BarItemImageSize int
           ,@BarItemTypeId int
           ,@BarItemOrder int
as
UPDATE [Genesis].[dbo].[Module_BarItem]
   SET [ParentBarItemGuid] = @ParentBarItemGuid
      ,[RibbonBarGuid] = @RibbonBarGuid
      ,[CommandGuid] = @CommandGuid
      ,[BarItemName] = @BarItemName
      ,[BarItemImage] = @BarItemImage
      ,[BarItemImageSize] = @BarItemImageSize
      ,[BarItemTypeId] = @BarItemTypeId
      ,[BarItemOrder] = @BarItemOrder

WHERE BarItemGuid = @BarItemGuid

GO

/****** Object:  StoredProcedure [dbo].[update_Module_Command]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[update_Module_Command]

			 @CommandGuid uniqueidentifier
            ,@FileGuid uniqueidentifier
            ,@CommandName nvarchar(50)
            ,@CommandCode nvarchar(50)
            ,@CommandDescription ntext
            ,@CommandType nvarchar(512)

as

UPDATE [Genesis].[dbo].[Module_Command]
   SET [FileGuid] = @FileGuid
      ,[CommandName] = @CommandName
      ,[CommandCode] = @CommandCode
      ,[CommandDescription] = @CommandDescription
      ,[CommandType] = @CommandType

 WHERE CommandGuid = @CommandGuid

GO

/****** Object:  StoredProcedure [dbo].[Update_Module_File]    Script Date: 07/21/2009 21:55:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Update_Module_File]
			@FileGuid uniqueidentifier
           ,@ApplicationGuid uniqueidentifier
           ,@FileName nvarchar(256)
           ,@FileDescription ntext
           ,@FileUrl nvarchar(1024)
           ,@FilePath nvarchar(256)
           ,@FileVersionMajor int
           ,@FileVersionMinor int
           ,@FileVersionBuild int
           ,@FileVersionRevision int
           ,@FileTypeId int
           ,@FileOrder int
as

UPDATE [Genesis].[dbo].[Module_File]
   SET [ApplicationGuid] = @ApplicationGuid
      ,[FileName] = @FileName
      ,[FileDescription] = @FileDescription
      ,[FileUrl] = @FileUrl
      ,[FilePath] = @FilePath
      ,[FileVersionMajor] = @FileVersionMajor
      ,[FileVersionMinor] = @FileVersionMinor
      ,[FileVersionBuild] = @FileVersionBuild
      ,[FileVersionRevision] = @FileVersionRevision
      ,[FileTypeId] = @FileTypeId
      ,[FileOrder] = @FileOrder
 WHERE [FileGuid] = @FileGuid

GO

/****** Object:  StoredProcedure [dbo].[Update_Module_Ribbon]    Script Date: 07/21/2009 21:55:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[Update_Module_Ribbon]
(		    @Ribbonguid uniqueidentifier
		   ,@RibbonName nvarchar(50)
           ,@ApplicationGuid uniqueidentifier
           ,@RibbonOrder int
)

as
UPDATE	 [Genesis].[dbo].[Module_Ribbon]
  set       [RibbonName]= @RibbonName
           ,[ApplicationGuid]= @ApplicationGuid
           ,[RibbonOrder]=@RibbonOrder
     

WHERE
Ribbonguid = @Ribbonguid

GO

/****** Object:  StoredProcedure [dbo].[Update_Module_RibbonBar]    Script Date: 07/21/2009 21:55:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[Update_Module_RibbonBar]
(		    @RibbonBarGuid uniqueidentifier
		   ,@RibbonBarName nvarchar(50)
           ,@RibbonGuid uniqueidentifier
           ,@RibbonBarOrder int
)

as
UPDATE	 [Genesis].[dbo].[Module_RibbonBar]
  set       [RibbonBarName]= @RibbonBarName
           ,[RibbonGuid] = @RibbonGuid
           ,[RibbonBarOrder]=@RibbonBarOrder
     

WHERE
RibbonBarGuid = @RibbonBarGuid

GO

/****** Object:  StoredProcedure [dbo].[Update_SecurityUser]    Script Date: 07/21/2009 21:55:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Update_SecurityUser]

(		    @UserGuid uniqueidentifier
		   ,@Username nvarchar(50)
           ,@Password nvarchar(255)
           ,@email nvarchar(128)
           ,@Active bit
           ,@StartupScript nvarchar(255)
)

as
UPDATE [Genesis].[dbo].[Security_User]
SET
            [Username] = @Username
           ,[Password] = @Password
           ,[email] = @email
           ,[Active] = @Active
           ,[StartupScript] = @StartupScript

WHERE
UserGuid = @UserGuid

GO

/****** Object:  StoredProcedure [dbo].[View_GenesisUserWithoutSession_Get]    Script Date: 07/21/2009 21:55:54 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets records from the View_GenesisUserWithoutSession view passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[View_GenesisUserWithoutSession_Get]
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN

				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				IF (@OrderBy is null or LEN(@OrderBy) < 1)
				BEGIN
					-- default order by to first column
					SET @OrderBy = '[UserGuid]'
				END

				-- SQL Server 2005 Paging
				declare @SQL as nvarchar(4000)
				SET @SQL = 'WITH PageIndex AS ('
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
				SET @SQL = @SQL + ', [UserGuid]'
				SET @SQL = @SQL + ', [UserId]'
				SET @SQL = @SQL + ', [Username]'
				SET @SQL = @SQL + ', [Password]'
				SET @SQL = @SQL + ', [email]'
				SET @SQL = @SQL + ', [Active]'
				SET @SQL = @SQL + ', [StartupScript]'
				SET @SQL = @SQL + ' FROM dbo.[View_GenesisUserWithoutSession]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				SET @SQL = @SQL + ' ) SELECT'
				SET @SQL = @SQL + ' [UserGuid],'
				SET @SQL = @SQL + ' [UserId],'
				SET @SQL = @SQL + ' [Username],'
				SET @SQL = @SQL + ' [Password],'
				SET @SQL = @SQL + ' [email],'
				SET @SQL = @SQL + ' [Active],'
				SET @SQL = @SQL + ' [StartupScript]'
				SET @SQL = @SQL + ' FROM PageIndex'
				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
				END
				exec sp_executesql @SQL

				-- get row count
				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
				SET @SQL = @SQL + ' FROM dbo.[View_GenesisUserWithoutSession]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				exec sp_executesql @SQL
				
				END

GO

/****** Object:  StoredProcedure [dbo].[View_GenesisUserWithoutSession_Get_List]    Script Date: 07/21/2009 21:55:54 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

/*
----------------------------------------------------------------------------------------------------
-- Date Created: 20 May 2007

-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)
-- Purpose: Gets all records from the View_GenesisUserWithoutSession view
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE [dbo].[View_GenesisUserWithoutSession_Get_List]

AS


				
				SELECT
					[UserGuid],
					[UserId],
					[Username],
					[Password],
					[email],
					[Active],
					[StartupScript]
				FROM
					dbo.[View_GenesisUserWithoutSession]
					
				Select @@ROWCOUNT

GO

